LEFT JOIN, RIGHT JOIN — farqi va qachon ishlatiladi

~20 daq · MySQL · JOIN va Munosabatlar

Muammodan boshlaylik

Marketing bo'limi so'raydi: "Barcha mijozlar ro'yxati — har birining buyurtmalari soni bilan. Hech narsa sotib olmaganlarni ham ko'rsating — ularga chegirma yuboramiz."

INNER JOIN bilan:

SELECT m.ism, COUNT(b.id) AS buyurtmalar
FROM mijozlar m
JOIN buyurtmalar b ON b.mijoz_id = m.id
GROUP BY m.id, m.ism;

+-----------------+-------------+
| ism             | buyurtmalar |
+-----------------+-------------+
| Aziz Karimov    |           2 |
| Malika Rahimova |           2 |
| Jasur Toshev    |           1 |
+-----------------+-------------+

Nodira yo'q. U hech narsa sotib olmagan — aynan chegirma kerak bo'lgan odam — va INNER JOIN uni tashlab yubordi.

LEFT JOIN

SELECT m.ism, COUNT(b.id) AS buyurtmalar
FROM mijozlar m
LEFT JOIN buyurtmalar b ON b.mijoz_id = m.id
GROUP BY m.id, m.ism;

+-----------------+-------------+
| ism             | buyurtmalar |
+-----------------+-------------+
| Aziz Karimov    |           2 |
| Malika Rahimova |           2 |
| Jasur Toshev    |           1 |
| Nodira Usmonova |           0 |
+-----------------+-------------+

LEFT JOIN — chap jadvalning (FROM dagi) barcha qatorlari albatta qoladi. O'ng jadvalda mosi bo'lmasa — o'ng tarafdagi ustunlar NULL bilan to'ldiriladi.

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

+-----------------+-------------+------------+
| ism             | buyurtma_id | holat      |
+-----------------+-------------+------------+
| Aziz Karimov    |           1 | yetkazildi |
| Aziz Karimov    |           2 | tolangan   |
| Malika Rahimova |           3 | yetkazildi |
| Malika Rahimova |           5 | yangi      |
| Jasur Toshev    |           4 | bekor      |
| Nodira Usmonova |        NULL | NULL       |
+-----------------+-------------+------------+

COUNT(b.id) — NULL larni sanamaydi (13-dars), shuning uchun Nodira uchun 0. COUNT(*) yozilsa — 1 chiqardi, chunki qator bor. LEFT JOIN da doim o'ng jadvalning ustunini sanang.

"Mosi yo'qlar" ni topish

LEFT JOIN ning eng foydali ishlatilishi — bog'lanishi yo'q qatorlarni topish:

-- Hech qachon buyurtma bermagan mijozlar
SELECT m.ism, m.email
FROM mijozlar m
LEFT JOIN buyurtmalar b ON b.mijoz_id = m.id
WHERE b.id IS NULL;

+-----------------+----------------+
| ism             | email          |
+-----------------+----------------+
| Nodira Usmonova | nodira@mail.uz |
+-----------------+----------------+

-- Hech qachon sotilmagan mahsulotlar
SELECT p.nom, p.soni
FROM mahsulotlar p
LEFT JOIN buyurtma_qatorlari q ON q.mahsulot_id = p.id
WHERE q.id IS NULL;
-- Lenovo IdeaPad

-- Mahsulotsiz toifalar
SELECT t.nom
FROM toifalar t
LEFT JOIN mahsulotlar p ON p.toifa_id = t.id
WHERE p.id IS NULL;

Bu naqsh — anti-join. Uni yod oling: LEFT JOIN ... WHERE o'ng.id IS NULL.

Muhim tuzoq: shart ON da yoki WHERE da?

Barcha mijozlar va ularning faqat yetkazilgan buyurtmalari soni:

-- ❌ Nodira va Jasur yo'qoldi
SELECT m.ism, COUNT(b.id) AS yetkazilgan
FROM mijozlar m
LEFT JOIN buyurtmalar b ON b.mijoz_id = m.id
WHERE b.holat = 'yetkazildi'
GROUP BY m.id, m.ism;

Nima bo'ldi? LEFT JOIN Nodirani NULL lar bilan saqladi. Keyin WHERE b.holat = 'yetkazildi' — NULL = 'yetkazildi' → NULL → qator tashlandi. WHERE dagi o'ng jadval sharti LEFT JOIN ni INNER JOIN ga aylantirib qo'ydi.

-- ✅ Shartni ON ga ko'chiring
SELECT m.ism, COUNT(b.id) AS yetkazilgan
FROM mijozlar m
LEFT JOIN buyurtmalar b ON b.mijoz_id = m.id AND b.holat = 'yetkazildi'
GROUP BY m.id, m.ism;

+-----------------+-------------+
| ism             | yetkazilgan |
+-----------------+-------------+
| Aziz Karimov    |           1 |
| Malika Rahimova |           1 |
| Jasur Toshev    |           0 |
| Nodira Usmonova |           0 |
+-----------------+-------------+

Qoida LEFT JOIN uchun:

• O'ng jadval bo'yicha shart → ON ga ("qaysi buyurtmalarni qo'shish")
• Chap jadval bo'yicha shart → WHERE ga ("qaysi mijozlarni ko'rsatish")

RIGHT JOIN

RIGHT JOIN — LEFT JOIN ning ko'zgudagi aksi: o'ng jadvalning barcha qatorlari qoladi.

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

Bu aynan shu bilan bir xil:

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

Amalda RIGHT JOIN deyarli ishlatilmaydi — jadvallar tartibini almashtirib, LEFT JOIN yozish ancha o'qishli: "asosiy jadval — FROM da, qo'shimchalar — o'ngga".

Bir nechta LEFT JOIN

Barcha mahsulotlar — toifasi bilan (toifasiz "Sovg'a kartasi" ham) va sotilgan soni bilan (sotilmaganlari ham):

SELECT
    p.nom,
    COALESCE(t.nom, 'Toifasiz')   AS toifa,
    COALESCE(SUM(q.soni), 0)      AS sotilgan
FROM mahsulotlar p
LEFT JOIN toifalar t           ON t.id = p.toifa_id
LEFT JOIN buyurtma_qatorlari q ON q.mahsulot_id = p.id
GROUP BY p.id, p.nom, t.nom
ORDER BY sotilgan DESC;

COALESCE(x, standart) — NULL o'rniga tushunarli qiymat.

[!WARNING] LEFT JOIN zanjiridan keyin oddiy JOIN qo'shsangiz — u NULL qatorlarni yana tashlab yuboradi. Bir marta LEFT JOIN boshlagan bo'lsangiz, o'sha tarmoqdagi keyingi JOIN lar ham odatda LEFT bo'lishi kerak.

Qaysi JOIN ni tanlash?

Oddiy test: "chap jadvaldagi biror qator natijadan tushib qolsa — bu to'g'rimi?" Yo'q bo'lsa — LEFT JOIN.

Nima uchun shunday?

Nima uchun LEFT JOIN mosi yo'q qatorlarni NULL bilan to'ldiradi — bo'sh qoldirsa bo'lmaydimi?

SQL natijasi — doim to'g'ri to'rtburchak jadval: har bir qatorda bir xil ustunlar. Nodiraning qatorida buyurtma_id ustuni bo'lishi shart — chunki boshqa qatorlarda bor. U yerga nima yozish kerak? "Bu qiymat mavjud emas" ni bildiruvchi yagona belgi — NULL. Shu sababli IS NULL bilan "mosi yo'qlar" ni topish mumkin.

Xulosa

Eslab qoling:

• LEFT JOIN — chap jadvalning barcha qatorlari; mosi yo'q bo'lsa o'ng tomon NULL
• Sanashda — COUNT(o'ng.id), COUNT(*) emas
• Anti-join: LEFT JOIN ... WHERE o'ng.id IS NULL — bog'lanishi yo'qlarni topish
• O'ng jadval sharti → ON ga; WHERE ga yozsangiz — INNER JOIN bo'lib qoladi
• RIGHT JOIN = jadvallar almashtirilgan LEFT JOIN; LEFT ni afzal ko'ring
• COALESCE(ustun, 'standart') — NULL larni chiroyli ko'rsatish

Keyingi dars

17-dars: FULL JOIN, CROSS JOIN, SELF JOIN