JOIN lar

5-bob 12 kod bloki 4141 so'z

1-bobda data nima uchun table larga bo'linishi tushuntirilgan edi: har bir fakt aynan bir marta saqlanadi, shunda u o'zi bilan zid bo'lolmaydi. JOIN lar — shu kelishuvning ikkinchi yarmi: ular bo'laklarni bitta query davomida qayta birlashtiradi.

Bu — talabalar SQL ni yo "tushunib oladigan", yo indamay tushunmay qoladigan bob, shuning uchun shoshilmang va query yozishdan oldin doim ikkala table ni chizib bering.

JOIN nima uchun kerak

enrollments aslida nima saqlashiga qarang:

SELECT * FROM enrollments LIMIT 3;

enrollment_idstudent_idcourse_idteacher_idenrolled_atgradestatus

11142025-09-0592completed 21222025-09-0588completed 31512026-02-03NULLactive

Buni hech kim o'qiy olmaydi. U "1 4 bilan 1 ni o'qidi" deydi — uchta raqam va hech qanday ma'no. Odamga "Aziza Python Basics ni Sardor Yoqubov bilan o'qidi" kerak. Birinchi table ni ikkinchi jumlaga aylantirish — JOIN ning butun vazifasi.

INNER JOIN

INNER JOIN faqat ikkala tomonda mos kelgan row larni qoldiradi. U standart — oddiy JOIN INNER JOIN degani.

SELECT
    s.first_name,
    s.last_name,
    e.grade,
    e.status
FROM enrollments e
INNER JOIN students s ON e.student_id = s.student_id
ORDER BY e.enrollment_id
LIMIT 5;

5 qator

1 ms

ON, USING va eski vergul sintaksisi

-- 1. ON — doim ishlaydi va column nomlari har xil bo'lsa yagona variant
SELECT s.first_name, e.grade
FROM students s JOIN enrollments e ON s.student_id = e.student_id;

-- 2. USING — qisqaroq, lekin faqat IKKALA column nomi bir xil bo'lganda
SELECT s.first_name, e.grade
FROM students s JOIN enrollments e USING (student_id);

-- 3. Eski vergul sintaksisi — yashirin inner join. Buni o'rgatmang.
SELECT s.first_name, e.grade
FROM students s, enrollments e
WHERE s.student_id = e.student_id;

1-natija

… 2500 qatordan birinchi 15 tasi

2500 qator

2-natija

… 2500 qatordan birinchi 15 tasi

2500 qator

3-natija

… 2500 qatordan birinchi 15 tasi

2500 qator

4 ms

:::gotcha ⚠️ Nega vergul sintaksisi zamonaviy kodda taqiqlangan WHERE qatorini unutsangiz, xato chiqmaydi — CROSS JOIN chiqadi: har bir student har bir enrollment bilan juftlanadi, 10 × 20 = 200 ta ma'nosiz row. Aniq JOIN ... ON da esa join sharti join ning yonida turadi va yo'qolib qololmaydi. Vergul bilan outer join yozishning ham iloji yo'q. JOIN ... ON ishlating. :::

LEFT JOIN

LEFT JOIN chap table ning har bir row ini saqlaydi — mos kelgan-kelmaganidan qat'i nazar. Mos kelmagan joyda o'ng table ning column lari NULL bo'lib qaytadi.

Bu aynan 4-bobdagi yo'qolgan course ning yechimi:

SELECT
    c.title,
    COUNT(e.enrollment_id) AS enrollments,
    ROUND(AVG(e.grade), 1) AS avg_grade
FROM courses c
LEFT JOIN enrollments e ON c.course_id = e.course_id
GROUP BY c.course_id, c.title
ORDER BY enrollments DESC;

15 qator

1 ms

Deep Learning0NULL

Deep Learning nihoyat paydo bo'ldi, 0 bilan. Aynan shu bitta row odatda butun hisobotdagi eng qimmatli qator — hech kim yozilmagan course rahbariyat ko'rishi kerak bo'lgan narsaning o'zi.

Xuddi shu shakl NULL teacher uchun ham ishlaydi:

SELECT
    e.enrollment_id,
    COALESCE(t.full_name, '(biriktirilmagan)') AS teacher
FROM enrollments e
LEFT JOIN teachers t ON e.teacher_id = t.teacher_id
WHERE e.enrollment_id IN (18, 19, 20);

3 qator

0 ms

RIGHT va FULL OUTER JOIN

RIGHT JOIN — ko'zgudagi aksi: u o'ng table ning har bir row ini saqlaydi. FULL OUTER JOIN ikkala tomonning ham hamma row ini saqlaydi va yetishmagan tomonni NULL bilan to'ldiradi.

FULL OUTER JOIN analytics da kam uchraydi, lekin solishtirish uchun haqiqatan foydali — bir xil yozuvlarni saqlashi kerak bo'lgan ikkita tizimni taqqoslab, qaysi tomonda nima yetishmayotganini topish. Qo'llab-quvvatlanmagan joyda uni ikkita LEFT JOIN va UNION bilan yasash mumkin (9-bob).

Anti-join: mos kelmagan row lar

"Qaysi X da Y yo'q?" — analytics dagi eng ko'p uchraydigan savollardan biri: hech qachon buyurtma bermagan mijozlar, sotilmagan mahsulotlar, yozilmagan student lar. Pattern doim bir xil — LEFT JOIN qiling, keyin o'ng tomoni NULL bo'lib qaytgan row larni qoldiring.

SELECT c.course_id, c.title
FROM courses c
LEFT JOIN enrollments e ON c.course_id = e.course_id
WHERE e.enrollment_id IS NULL;

1 qator

1 ms

-- (a) LEFT JOIN + IS NULL — odatda eng tezi
SELECT c.title FROM courses c
LEFT JOIN enrollments e ON c.course_id = e.course_id
WHERE e.enrollment_id IS NULL;

-- (b) NOT EXISTS — maqsad eng aniq, NULL ga nisbatan xavfsiz
SELECT c.title FROM courses c
WHERE NOT EXISTS (SELECT 1 FROM enrollments e WHERE e.course_id = c.course_id);

-- (c) NOT IN — faqat enrollments.course_id NOT NULL bo'lgani uchun xavfsiz. Mo'rt.
SELECT c.title FROM courses c
WHERE c.course_id NOT IN (SELECT course_id FROM enrollments);

1-natija

1 qator

2-natija

1 qator

3-natija

1 qator

1 ms

:::tip 💡 Tekshiradigan column ni ehtiyot bo'lib tanlang (a) pattern ida o'ng table ning real data da hech qachon NULL bo'lmaydigan column ini tekshiring — uning primary key i xavfsiz tanlov. e.grade kabi NULL bo'lishi mumkin column ni tekshirsangiz, mavjud lekin baholanmagan enrollment lar ham tushib qoladi va javob noto'g'ri bo'ladi. :::

Uch va undan ko'p table ni JOIN qilish

JOIN lar zanjir bo'ladi: bitta join ning natijasi — table, u keyingisiga join qilinadi. Junction table (enrollments) dan boshlang va lookup table larni unga osib chiqing.

SELECT
    s.first_name || ' ' || s.last_name    AS student,
    c.title                               AS course,
    COALESCE(t.full_name, '(yo''q)')      AS teacher,
    e.grade,
    e.status
FROM enrollments e
JOIN      students s ON e.student_id = s.student_id
JOIN      courses  c ON e.course_id  = c.course_id
LEFT JOIN teachers t ON e.teacher_id = t.teacher_id
ORDER BY e.enrollment_id
LIMIT 6;

6 qator

1 ms

CROSS JOIN va SELF JOIN

CROSS JOIN

A ning har bir row i B ning har bir row i bilan — shartsiz. Natijada m × n ta row bo'ladi.

SELECT COUNT(*) FROM students CROSS JOIN courses; -- 10 × 6 = 60 U deyarli doim tasodif (unutilgan ON), lekin bitta haqiqiy foydasi bor: nol larni ko'rsatish uchun to'liq to'r qurish. "Har bir course, har bir oy, hatto bo'sh birikmalar ham" ni yolg'iz GROUP BY bilan olishning iloji yo'q — avval to'rni yaratish, keyin unga real data ni LEFT JOIN qilish kerak.

SELF JOIN

Table ning o'ziga o'zi join qilinishi; engine nusxalarni ajrata olishi uchun ikkita har xil alias ishlatiladi. Uni bitta table ichida row larni solishtirish uchun ishlating.

-- Bir shaharda yashaydigan student juftliklari
-- (a.student_id < b.student_id o'z-o'ziga juftlanishni VA takroriy aksni to'sadi)
SELECT
    a.first_name AS student_a,
    b.first_name AS student_b,
    a.city
FROM students a
JOIN students b ON a.city = b.city AND a.student_id < b.student_id
ORDER BY a.city;

… 6550 qatordan birinchi 15 tasi

6550 qator

4 ms

student_astudent_bcity

BekzodZarinaSamarkand AzizaJasurTashkent AzizaOtabekTashkent JasurOtabekTashkent

Sevara yo'q, chunki uning shahri NULL, NULL = NULL esa UNKNOWN — 1-bobdagi NULL qoidasi ON ichida ham xuddi WHERE dagidek ishlaydi.

Fan-out: row larni ko'paytiruvchi jimjit xato

Bu — JOIN dagi eng qimmat xato, chunki query mukammal ishlaydi va ishonarli ko'rinadigan, lekin shunchaki noto'g'ri raqam qaytaradi.

SELECT COUNT(*) FROM students;                                  -- 10

SELECT COUNT(*) FROM students s
JOIN enrollments e ON s.student_id = e.student_id;              -- 20  ← !!

SELECT COUNT(DISTINCT s.student_id) FROM students s
JOIN enrollments e ON s.student_id = e.student_id;              -- 10

1-natija

1 qator

2-natija

1 qator

3-natija

1 qator

1 ms

"Ko'p" tomonga join qilish har bir student row ini takrorladi — har bir enrollment uchun bir marta. Endi sanash student larni emas, enrollment larni sanayapti. Pul bilan bo'lsa, battarroq:

-- Teacher larga to'lanadigan umumiy oylik qancha?
SELECT SUM(salary) FROM teachers;
-- 36 500 000  — to'g'ri

-- Xuddi shu savol, lekin kimdir course bo'yicha filtrlash uchun enrollments ni join qilgan
SELECT SUM(t.salary) FROM teachers t
JOIN enrollments e ON t.teacher_id = e.teacher_id;
-- 175 600 000  — deyarli 5 barobar ko'p, va hech qayerda xato yo'q

1-natija

1 qator

2-natija

1 qator

1 ms

Nodira ning oyligi 6 marta sanaldi (u 6 ta enrollment da bor), Rustam niki 5 marta va hokazo. Hisobot professional ko'rinadi va 139 million ga xato.

Outer join da ON va WHERE

INNER JOIN da shartni qayerga qo'yish farq qilmaydi. LEFT JOIN da esa u javobni butunlay o'zgartiradi, va bu eng yuqori darajadagi intervyu savollaridan biri.

-- (A) shart ON ichida: O'NG table ni join dan OLDIN filtrlaydi.
--     Har bir course baribir saqlanadi.
SELECT c.title, COUNT(e.enrollment_id) AS completed
FROM courses c
LEFT JOIN enrollments e
       ON c.course_id = e.course_id AND e.status = 'completed'
GROUP BY c.course_id, c.title;

-- (B) shart WHERE ichida: NATIJANI join dan KEYIN filtrlaydi.
--     Tugatilgan enrollment i yo'q course lar o'chadi — LEFT JOIN
--     indamay INNER JOIN ga aylandi.
SELECT c.title, COUNT(e.enrollment_id) AS completed
FROM courses c
LEFT JOIN enrollments e ON c.course_id = e.course_id
WHERE e.status = 'completed'
GROUP BY c.course_id, c.title;

1-natija

15 qator

2-natija

14 qator

2 ms

Deep Learning0row yo'qoladi

:::info 📌 Qoida LEFT JOIN da: o'ng table ga oid shartlar ON ga, chap table ga oid shartlar WHERE ga yoziladi. O'ng table sharti WHERE ga tushsa, outer join yana inner join ga aylanadi — istisno faqat IS NULL, u anti-join pattern i va ataylab qilinadi. :::

Mashqlar to'plami

-- 1. Har bir student va u oladigan course lar soni (nol bo'lsa ham)
SELECT s.first_name, COUNT(e.enrollment_id) AS courses
FROM students s LEFT JOIN enrollments e ON s.student_id = e.student_id
GROUP BY s.student_id, s.first_name ORDER BY courses DESC;

-- 2. Teacher lar va ular olib boradigan enrollment lar soni
SELECT t.full_name, t.subject, COUNT(e.enrollment_id) AS lessons
FROM teachers t LEFT JOIN enrollments e ON t.teacher_id = e.teacher_id
GROUP BY t.teacher_id, t.full_name, t.subject ORDER BY lessons DESC;

-- 3. 'SQL for Analytics' ni qaysi student lar oladi?
SELECT s.first_name, s.last_name, e.grade
FROM enrollments e
JOIN students s ON e.student_id = s.student_id
JOIN courses  c ON e.course_id  = c.course_id
WHERE c.title = 'SQL for Analytics' ORDER BY e.grade DESC;

-- 4. Hech kim yozilmagan course lar (anti-join)
SELECT c.title FROM courses c
LEFT JOIN enrollments e ON c.course_id = e.course_id
WHERE e.enrollment_id IS NULL;

-- 5. Kategoriya bo'yicha daromad (narx har bir enrollment uchun bir marta)
SELECT c.category, COUNT(*) AS enrollments, SUM(c.price) AS revenue
FROM enrollments e JOIN courses c ON e.course_id = c.course_id
GROUP BY c.category ORDER BY revenue DESC;

-- 6. Shahar bo'yicha o'rtacha baho
SELECT COALESCE(s.city,'(unknown)') AS city,
       COUNT(e.grade) AS graded, ROUND(AVG(e.grade),1) AS avg_grade
FROM students s JOIN enrollments e ON s.student_id = e.student_id
GROUP BY s.city ORDER BY avg_grade DESC;

-- 7. To'liq o'qishli enrollment hisoboti
SELECT s.first_name || ' ' || s.last_name AS student, c.title AS course,
       COALESCE(t.full_name,'(yo''q)') AS teacher, e.grade, e.status
FROM enrollments e
JOIN students s ON e.student_id = s.student_id
JOIN courses  c ON e.course_id  = c.course_id
LEFT JOIN teachers t ON e.teacher_id = t.teacher_id
ORDER BY student, course;

-- 8. Bir shahardagi student lar (self join, faqat bitta yo'nalish)
SELECT a.first_name AS a, b.first_name AS b, a.city
FROM students a JOIN students b
  ON a.city = b.city AND a.student_id < b.student_id;

1-natija

… 300 qatordan birinchi 15 tasi

300 qator

2-natija

12 qator

3-natija

… 186 qatordan birinchi 15 tasi

186 qator

4-natija

1 qator

5-natija

5 qator

6-natija

9 qator

7-natija

… 2500 qatordan birinchi 15 tasi

2500 qator

8-natija

… 6550 qatordan birinchi 15 tasi

6550 qator

13 ms

Intervyu savollari

S: INNER JOIN va LEFT JOIN farqi?

INNER faqat ikkala tomonda mos kelgan row larni qaytaradi. LEFT chap table ning har bir row ini qaytaradi va o'ng tomon mos kelmagan joyda NULL qo'yadi. "Nol" ma'noli javob bo'lgan har qanday holatda LEFT ishlating.

S: LEFT JOIN dan keyin nega COUNT(*) noto'g'ri javob beradi?

Chunki mos kelmagan chap row lar baribir bitta chiqish row i hosil qiladi (o'ng tomoni NULL), COUNT(*) esa row larni sanaydi. Buning o'rniga o'ng table ning NULL bo'lmaydigan column ini sanang: COUNT(e.enrollment_id).

S: LEFT JOIN da shartni ON ga va WHERE ga qo'yish orasida qanday farq bor?

ON o'ng table ni join dan oldin filtrlaydi, shuning uchun mos kelmagan chap row lar saqlanadi. WHERE join dan keyin filtrlaydi, ya'ni NULL bilan to'ldirilgan row larni olib tashlaydi va LEFT JOIN ni amalda INNER JOIN ga aylantiradi.

S: A da bor, B da mos kelmaydigan row larni qanday topasiz?

A LEFT JOIN B ON ... WHERE B.primary_key IS NULL, yoki NOT EXISTS. Column NULL bo'lmasligi kafolatlanmagan bo'lsa, NOT IN dan qoching (3-bob).

S: Self join nima va u qachon kerak?

Table ning ikkita alias ostida o'ziga o'zi join qilinishi — bitta table ichidagi row larni solishtirish uchun: xodim/rahbar iyerarxiyasi, umumiy atributga ega juftliklar, ketma-ket hodisalar. O'z-o'ziga juftlanish va takroriy akslarni to'sish uchun a.id < b.id qo'shing.

S: JOIN qo'shganingizdan keyin SUM ikki barobar oshdi. Nima bo'ldi?

Fan-out: join ko'p tomonda bir nechta row ga mos keldi, bu chap row larni va demak qo'shilayotgan qiymatlarni takrorladi. Yechim — ko'p tomonni avval aggregate qilish (CTE yoki derived table), keyin join qilish; DISTINCT emas.

S: JOIN tartibi natijaga ta'sir qiladimi?

Inner join lar uchun yo'q — ular kommutativ va optimizer ularni erkin qayta tartiblaydi. Outer join lar uchun ha: LEFT JOIN dan keyin qo'yilgan INNER JOIN LEFT JOIN saqlab qolgan NULL li row larni o'chirib yuborishi mumkin.

S: CROSS JOIN nimaga kerak?

Birikmalarning to'liq to'rini yaratish uchun — har bir mahsulot × har bir oy — keyin unga real data ni LEFT JOIN qilasiz, shunda bo'sh birikmalar yo'qolmay 0 bo'lib chiqadi.

Xulosa

• JOIN mos row larni yonma-yon yopishtiradi. JOIN turlari orasidagi yagona farq — mos kelmagan row lar taqdiri.
• INNER JOIN = faqat mos kelganlar. LEFT JOIN = barcha chap row lar + mos kelganlar. RIGHT = LEFT ning aksi. FULL = ikkala tomon. CROSS = barcha birikmalar.
• Aniq JOIN ... ON ishlating. Vergul join va NATURAL JOIN dan qoching.
• LEFT JOIN dan keyin o'ng table column ini sanang, hech qachon COUNT(*) ni emas — aks holda nol lar birga aylanadi.
• Anti-join = LEFT JOIN ... WHERE right_pk IS NULL — "qaysi X da Y yo'q?" savolining javobi.
• ON oldin, WHERE keyin filtrlaydi. O'ng table sharti WHERE da bo'lsa, LEFT JOIN indamay INNER JOIN ga aylanadi.
• Bir marta LEFT ga o'tsangiz, LEFT da qoling — keyingi INNER JOIN uni bekor qiladi.
• Fan-out — xavfli xato: ko'p tomonga join qilish row larni takrorlaydi va har bir SUM va COUNT ni shishiradi. Row sonini oldin va keyin tekshiring; avval aggregate qiling, keyin join.

Keyingi: 6-bob — subquery va CTE lar, ya'ni bitta query natijasini boshqasiga kirish sifatida ishlatish vositalari.