INNER JOIN — ikkita jadval birlashtirish

~20 daq · MySQL · JOIN va Munosabatlar

Muammodan boshlaylik

buyurtmalar jadvalidan oxirgi buyurtmalarni olamiz:

SELECT id, mijoz_id, holat FROM buyurtmalar;

+----+----------+------------+
| id | mijoz_id | holat      |
+----+----------+------------+
|  1 |        1 | yetkazildi |
|  2 |        1 | tolangan   |
|  3 |        2 | yetkazildi |
...

Operator so'raydi: "mijoz_id = 1 — bu kim? Telefoni qanday?" Ma'lumot ikki jadvalda: buyurtma buyurtmalar da, mijoz ismi mijozlar da. Ularni bitta natijaga birlashtirish kerak.

Normalizatsiya tufayli ma'lumot ko'p jadvallarga bo'lingan (1-dars). JOIN — ularni so'rov paytida qaytadan birlashtirish usuli.

INNER JOIN

SELECT b.id, m.ism, m.telefon, b.holat, b.yaratilgan
FROM buyurtmalar b
INNER JOIN mijozlar m ON m.id = b.mijoz_id;

+----+-----------------+---------------+------------+---------------------+
| id | ism             | telefon       | holat      | yaratilgan          |
+----+-----------------+---------------+------------+---------------------+
|  1 | Aziz Karimov    | +998901112233 | yetkazildi | 2026-09-05 10:15:00 |
|  2 | Aziz Karimov    | +998901112233 | tolangan   | 2026-10-12 18:40:00 |
|  3 | Malika Rahimova | +998935554433 | yetkazildi | 2026-10-02 12:00:00 |
|  4 | Jasur Toshev    | NULL          | bekor      | 2026-10-20 09:30:00 |
|  5 | Malika Rahimova | +998935554433 | yangi      | 2026-11-01 16:20:00 |
+----+-----------------+---------------+------------+---------------------+

Tuzilishi:

  • FROM buyurtmalar b — asosiy jadval, b — qisqa taxallus
  • INNER JOIN mijozlar m — qo'shiladigan jadval
  • ON m.id = b.mijoz_id — qaysi qatorlar bir-biriga mos kelishi sharti

MySQL har bir buyurtma uchun mijozlar dan id si mijoz_id ga teng qatorni topadi va ikkalasini yonma-yon qo'yadi.

INNER so'zi ixtiyoriy — shunchaki JOIN ham INNER JOIN degani.

INNER JOIN qanday ishlaydi

buyurtmalar                 mijozlar
id | mijoz_id               id | ism
1  | 1        ─────────→    1  | Aziz
2  | 1        ─────────→    1  | Aziz
3  | 2        ─────────→    2  | Malika
4  | 3        ─────────→    3  | Jasur
5  | 2        ─────────→    2  | Malika
                            4  | Nodira   ← hech qanday buyurtma yo'q

INNER JOIN faqat ikkala tomonda mosi bor qatorlarni qaytaradi. Nodira natijada yo'q — chunki uning buyurtmasi yo'q. Agar biror buyurtmaning mijozi topilmasa — u ham natijadan tushib qoladi.

Venn diagrammasida — ikki doiraning kesishmasi.

Taxalluslar va ustun nomlari

Ikkala jadvalda bir xil nomli ustunlar bor: id, yaratilgan. Qaysi birini nazarda tutyapsiz — aniq yozish kerak:

SELECT id, ism FROM buyurtmalar b JOIN mijozlar m ON m.id = b.mijoz_id;
-- ERROR 1052: Column 'id' in field list is ambiguous

SELECT b.id AS buyurtma_id, m.id AS mijoz_id, m.ism
FROM buyurtmalar b
JOIN mijozlar m ON m.id = b.mijoz_id;

Qoida: JOIN li so'rovda har bir ustun oldiga taxallus qo'ying (b.holat, m.ism) — hatto noyob bo'lsa ham. Kodni o'qiyotgan odam ustun qaysi jadvaldan ekanini darhol ko'radi.

WHERE, ORDER BY bilan birga

-- Toshkentlik mijozlarning yetkazilmagan buyurtmalari
SELECT b.id, m.ism, b.holat
FROM buyurtmalar b
JOIN mijozlar m ON m.id = b.mijoz_id
WHERE m.shahar = 'Toshkent'
  AND b.holat NOT IN ('yetkazildi', 'bekor')
ORDER BY b.yaratilgan DESC;

ON — jadvallarni qanday bog'lash. WHERE — natijani qanday filtrlash. INNER JOIN da filtr shartini ON ga yozsangiz ham natija bir xil bo'ladi, lekin aralashtirmang — o'qishli bo'lsin.

Uch va undan ko'p jadval

Buyurtmaning to'liq tarkibi: kim, nima, qancha:

SELECT
    b.id          AS buyurtma,
    m.ism         AS mijoz,
    p.nom         AS mahsulot,
    q.soni,
    q.narx,
    q.soni * q.narx AS summa
FROM buyurtmalar b
JOIN mijozlar m           ON m.id = b.mijoz_id
JOIN buyurtma_qatorlari q ON q.buyurtma_id = b.id
JOIN mahsulotlar p        ON p.id = q.mahsulot_id
WHERE b.id = 3;

+----------+-----------------+----------------+------+----------+----------+
| buyurtma | mijoz           | mahsulot       | soni | narx     | summa    |
+----------+-----------------+----------------+------+----------+----------+
|        3 | Malika Rahimova | MacBook Air M3 |    1 | 16900000 | 16900000 |
|        3 | Malika Rahimova | AirPods Pro    |    1 |  3100000 |  3100000 |
+----------+-----------------+----------------+------+----------+----------+

Har bir JOIN — yana bitta jadvalni zanjirga qo'shadi. Tartibni sxema bo'yicha kuzating:

mijozlar ← buyurtmalar ← buyurtma_qatorlari → mahsulotlar → toifalar

JOIN + GROUP BY

Har bir mahsulot qancha sotilgan:

SELECT p.nom, SUM(q.soni) AS sotilgan, SUM(q.soni * q.narx) AS tushum
FROM mahsulotlar p
JOIN buyurtma_qatorlari q ON q.mahsulot_id = p.id
JOIN buyurtmalar b        ON b.id = q.buyurtma_id
WHERE b.holat <> 'bekor'
GROUP BY p.id, p.nom
ORDER BY tushum DESC;

Hech qachon sotilmagan mahsulotlar (Lenovo, iPhone — bekor qilingan buyurtmada edi) natijada yo'q — INNER JOIN ularni tashlab yubordi. Ularni ham ko'rsatish kerak bo'lsa — keyingi darsdagi LEFT JOIN.

Qatorlar "ko'payishi"

JOIN dan keyin qatorlar soni o'zgarishi mumkin:

SELECT COUNT(*) FROM buyurtmalar;                                  -- 5
SELECT COUNT(*) FROM buyurtmalar b
JOIN buyurtma_qatorlari q ON q.buyurtma_id = b.id;                 -- 8

Bitta buyurtmada 2 ta qator bo'lsa — buyurtma natijada ikki marta chiqadi. Bu xato emas, JOIN shunday ishlaydi. Lekin aggregate larda ehtiyot bo'ling:

-- ❌ Buyurtmalar emas, qatorlar sanaldi
SELECT m.ism, COUNT(*) FROM mijozlar m
JOIN buyurtmalar b ON b.mijoz_id = m.id
JOIN buyurtma_qatorlari q ON q.buyurtma_id = b.id
GROUP BY m.id, m.ism;

-- ✅
SELECT m.ism, COUNT(DISTINCT b.id) ...

Eski sintaksis

Eski kodda shunday yozuvni ko'rasiz:

SELECT b.id, m.ism
FROM buyurtmalar b, mijozlar m
WHERE m.id = b.mijoz_id;

Bu ham INNER JOIN. Lekin WHERE dagi bog'lanish shartini unutsangiz — har bir buyurtma har bir mijoz bilan birlashadi (5 × 4 = 20 qator, 17-darsdagi CROSS JOIN). Aniq JOIN ... ON yozing — bog'lanish sharti hech qachon yo'qolmaydi.

Nima uchun shunday?

Nima uchun ma'lumotni bo'lib saqlab, keyin har safar JOIN bilan qaytadan yig'amiz — sekin emasmi?

Primary key va foreign key larda indeks bor. ON m.id = b.mijoz_id uchun MySQL har bir buyurtmaning mijozini indeks orqali bir zumda topadi — butun mijozlar jadvalini ko'rib chiqmaydi. Million buyurtma va yuz ming mijoz bo'lsa ham JOIN millisekundlarda bajariladi. Evaziga — har bir ma'lumot bitta joyda, o'zgartirish oson, ziddiyatlar yo'q. Bu almashuv deyarli har doim foydali.

Xulosa

Eslab qoling:

  • FROM a JOIN b ON b.ustun = a.ustun — ikki jadvalni bog'lash
  • INNER JOIN — faqat ikkala tomonda mosi borlar
  • Har bir ustunga taxallus: b.id, m.ism
  • ON — bog'lash sharti, WHERE — filtr
  • Ko'p jadval — JOIN zanjiri
  • JOIN dan keyin qatorlar takrorlanadi — COUNT(DISTINCT ...)
  • Eski FROM a, b WHERE ... sintaksisidan qoching

Keyingi dars

16-dars: LEFT JOIN, RIGHT JOIN — farqi va qachon ishlatiladi