Subquery va CTE

6-bob 11 kod bloki 3451 so'z

2-bobda query table qaytaradi degan fikr aytilgan edi. Bu bob shundan foyda oladi: agar query table qaytarsa, uni table kutilayotgan har qanday joyda ishlatish mumkin — WHERE ichida, FROM ichida, hatto SELECT ichida ham. Subquery shunchaki shu.

Shu yerda chalkash query lar o'qishli bo'lib qoladi ham, chunki CTE besh qavat qavs o'rniga har bir qadamga nom berish imkonini beradi.

Subquery nima — uchta o'rin

Subquery (ichki query) — boshqa ifoda ichida, doim qavs ichida yozilgan SELECT. Uni qayerga qo'yishingiz qanday shakl qaytarishi kerakligini belgilaydi.

Scalar subquery

Bitta qiymat qaytaradigan subquery ni qiymat ishlatish mumkin bo'lgan har qanday joyda ishlatsa bo'ladi.

SELECT
    title,
    price,
    (SELECT ROUND(AVG(price)) FROM courses)        AS avg_price,
    price - (SELECT AVG(price) FROM courses)       AS diff_from_avg
FROM courses
ORDER BY price DESC;

15 qator

1 ms

WHERE da u "o'rtachaga / maksimumga nisbatan" savoliga javob beradi:

SELECT title, price FROM courses
WHERE price > (SELECT AVG(price) FROM courses);
-- Machine Learning, Deep Learning

SELECT enrollment_id, grade FROM enrollments
WHERE grade = (SELECT MAX(grade) FROM enrollments);
-- 13-enrollment, baho 100

1-natija

8 qator

2-natija

14 qator

1 ms

:::gotcha ⚠️ Bittadan ko'p row = runtime xato WHERE price = (SELECT price FROM courses) subquery bittadan ko'p row qaytargan zahoti xato beradi. Ko'p qiymat bo'lishi mumkin bo'lsa, = o'rniga IN ishlating. Agar subquery hech qanday row qaytarmasa, u NULL bo'ladi — solishtirish UNKNOWN ga aylanadi va tashqi query indamay hech narsa qaytarmaydi. :::

Derived table (FROM dagi subquery)

FROM dagi subquery o'sha query uchun vaqtinchalik table bo'ladi. Unga alias berish shart. Bu — aggregate ustidan aggregate qilish usuli, buni bitta GROUP BY uddalay olmaydi.

-- "O'rtacha hisobda bitta course da nechta enrollment bor?"
SELECT ROUND(AVG(cnt), 2) AS avg_enrollments_per_course
FROM (
    SELECT course_id, COUNT(*) AS cnt
    FROM enrollments
    GROUP BY course_id
) AS per_course;
-- 4.0   (sanoqlar 5, 5, 3, 3, 4)

1 qator

1 ms

Ichki query 20 ta enrollment ni 5 ta row ga siqadi; tashqi query esa o'sha 5 ta sonning o'rtachasini oladi. Ikki daraja aggregation, ikkita query.

U 2-bobdagi alias bo'yicha filtrlab bo'lmaslik muammosini ham hal qiladi:

-- WHERE alias ni ko'rmaydi — lekin derived table ning column ini ko'radi
SELECT * FROM (
    SELECT title, ROUND(price * 1.0 / credits) AS per_credit
    FROM courses
) AS t
WHERE per_credit > 400000
ORDER BY per_credit DESC;
-- Deep Learning 600000, SQL for Analytics 500000,
-- Machine Learning 500000, Data Visualization 466667

3 qator

0 ms

Correlated subquery lar Oddiy subquery bir marta ishlaydi. Correlated subquery esa tashqi query ning column iga murojaat qiladi, shuning uchun u nazariy jihatdan har bir tashqi row uchun qaytadan ishlaydi.

SELECT
    s.first_name,
    (SELECT COUNT(*)     FROM enrollments e WHERE e.student_id = s.student_id) AS courses,
    (SELECT ROUND(AVG(grade),1) FROM enrollments e WHERE e.student_id = s.student_id) AS avg_grade
FROM students s
ORDER BY courses DESC, s.first_name;

… 300 qatordan birinchi 15 tasi

300 qator

35 ms

EXISTS va NOT EXISTS

EXISTS subquery ni oladi va u kamida bitta row hosil qilsa TRUE qaytaradi. Row larning ichida nima borligi uni qiziqtirmaydi — shuning uchun hamma uning ichiga SELECT 1 yozadi.

-- Kamida bitta active enrollment i bor student lar
SELECT s.first_name FROM students s
WHERE EXISTS (
    SELECT 1 FROM enrollments e
    WHERE e.student_id = s.student_id AND e.status = 'active'
);
-- Aziza, Dilnoza, Malika, Zarina, Ulugbek

-- Hech qachon yiqilmagan student lar
SELECT s.first_name FROM students s
WHERE NOT EXISTS (
    SELECT 1 FROM enrollments e
    WHERE e.student_id = s.student_id AND e.status = 'failed'
);
-- Jasur va Sevara dan boshqa hamma

1-natija

… 238 qatordan birinchi 15 tasi

238 qator

2-natija

… 160 qatordan birinchi 15 tasi

160 qator

22 ms

IN``EXISTS

Nimani solishtiradiQiymatni qiymatlar ro'yxati bilanRow mavjudmi yo'qmi Erta to'xtaydimiYo'q — butun ro'yxatni quradiHa — birinchi mos kelganda to'xtaydi Inkor qilinganda NULL ga xavfsizmiYo'q — NOT IN buziladiHa Nimaga yaxshiQisqa literal ro'yxatlarKatta table lardagi correlated tekshiruvlar

:::tip 💡 Standart tanlov — NOT EXISTS "Bog'liq row i yo'q" degani uchun NOT EXISTS NULL lardan qat'i nazar to'g'ri ishlaydi, ingliz jumlasidek o'qiladi va boshqa variantlardan kam bo'lmagan tezlikda ishlaydi. 3-bob NOT IN qanday buzilishini aynan ko'rsatgan edi. :::

CTE — WITH clause

CTE (Common Table Expression) — ichkariga ko'mib emas, asosiy query dan oldin nom berib yoziladigan derived table. Natija bir xil, o'qishlilik esa ancha yaxshi.

WITH student_stats AS (
    SELECT
        student_id,
        COUNT(*)      AS courses,
        AVG(grade)    AS avg_grade
    FROM enrollments
    WHERE grade IS NOT NULL
    GROUP BY student_id
)
SELECT
    s.first_name,
    st.courses,
    ROUND(st.avg_grade, 1) AS avg_grade
FROM student_stats st
JOIN students s ON s.student_id = st.student_id
WHERE st.avg_grade > (SELECT AVG(grade) FROM enrollments)
ORDER BY st.avg_grade DESC;

… 147 qatordan birinchi 15 tasi

147 qator

1 ms

Bir nechta CTE ni zanjirlash

Bir nechta CTE ni vergul bilan ajrating. Har biri o'zidan oldin e'lon qilinganlarga murojaat qila oladi, shuning uchun murakkab hisobot o'qishli pipeline ga aylanadi.

WITH graded AS (
    -- 1-qadam: faqat haqiqatan bahosi bor enrollment lar
    SELECT * FROM enrollments WHERE grade IS NOT NULL
),
course_avg AS (
    -- 2-qadam: har bir course bo'yicha o'rtacha
    SELECT course_id, COUNT(*) AS n, AVG(grade) AS avg_grade
    FROM graded GROUP BY course_id
),
overall AS (
    -- 3-qadam: butun maktab uchun bitta son
    SELECT AVG(grade) AS school_avg FROM graded
)
SELECT
    c.title,
    ca.n                                   AS graded,
    ROUND(ca.avg_grade, 1)                 AS course_avg,
    ROUND((SELECT school_avg FROM overall), 1) AS school_avg,
    CASE WHEN ca.avg_grade > (SELECT school_avg FROM overall)
         THEN 'yuqori' ELSE 'past' END     AS vs_school
FROM course_avg ca
JOIN courses c ON c.course_id = ca.course_id
ORDER BY ca.avg_grade DESC;

14 qator

1 ms

Recursive CTE

Recursive CTE o'ziga o'zi murojaat qiladi. Uning UNION ALL bilan birlashtirilgan ikkita qismi bor: anchor (boshlang'ich row lar) va rekursiv qadam (bir row dan keyingisiga qanday o'tish). Rekursiv qadam hech narsa qaytarmagan paytda u to'xtaydi.

WITH RECURSIVE numbers(n) AS (
    SELECT 1                       -- anchor: qayerdan boshlaymiz
    UNION ALL
    SELECT n + 1 FROM numbers      -- rekursiv qadam
    WHERE n < 10                   -- TO'XTASH sharti — buni hech qachon tashlab ketmang
)
SELECT n FROM numbers;
-- 1 2 3 4 5 6 7 8 9 10

10 qator

0 ms

Analytics uchun haqiqatan foydali variant — to'liq sana qatorini yaratish, shunda data si yo'q oylar ham hisobotda nol bo'lib chiqadi:

WITH RECURSIVE months(m) AS (
    SELECT '2025-09'
    UNION ALL
    SELECT strftime('%Y-%m', DATE(m || '-01', '+1 month'))
    FROM months WHERE m < '2026-09'
)
SELECT
    months.m                       AS month,
    COUNT(e.enrollment_id)         AS enrollments
FROM months
LEFT JOIN enrollments e ON strftime('%Y-%m', e.enrolled_at) = months.m
GROUP BY months.m
ORDER BY months.m;

13 qator

7 ms

Endi 13 ta oyning hammasi chiqadi — 2025-yil sentyabri 6 bilan, 2026-yil fevrali 12 bilan, 2026-yil sentyabri 2 bilan, va o'nta bo'sh oy 0 bilan. Yaratilgan qatorsiz o'sha oylar ko'rinmas edi, natijaning chiziqli grafigi esa juda chalg'ituvchi bo'lardi.

Yana bir klassik ishlatilishi — iyerarxiya bo'ylab yurish: xodimning butun rahbariyat zanjiri yoki chuqurligi noma'lum kategoriya daraxti.

Subquery, JOIN va CTE — qaysi birini tanlash

Mashqlar to'plami

-- 1. O'rtachadan qimmat course lar
SELECT title, price FROM courses
WHERE price > (SELECT AVG(price) FROM courses);

-- 2. Eng yuqori o'rtacha bahoga ega student(lar)
WITH avg_per_student AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM enrollments WHERE grade IS NOT NULL GROUP BY student_id
)
SELECT s.first_name, ROUND(a.avg_grade,1) AS avg_grade
FROM avg_per_student a JOIN students s USING (student_id)
WHERE a.avg_grade = (SELECT MAX(avg_grade) FROM avg_per_student);

-- 3. Machine Learning ni oladigan, lekin Python Basics ni olmaydigan student lar
SELECT s.first_name FROM students s
WHERE EXISTS     (SELECT 1 FROM enrollments e WHERE e.student_id = s.student_id AND e.course_id = 5)
  AND NOT EXISTS (SELECT 1 FROM enrollments e WHERE e.student_id = s.student_id AND e.course_id = 1);

-- 4. Bitta course ga to'g'ri keladigan o'rtacha enrollment soni
SELECT ROUND(AVG(cnt),2) FROM (
    SELECT course_id, COUNT(*) AS cnt FROM enrollments GROUP BY course_id
) t;

-- 5. Har bir course ning barcha enrollment lardagi ulushi
WITH per_course AS (
    SELECT course_id, COUNT(*) AS n FROM enrollments GROUP BY course_id
)
SELECT c.title, p.n,
       ROUND(100.0 * p.n / (SELECT SUM(n) FROM per_course), 1) AS pct_of_all
FROM per_course p JOIN courses c USING (course_id)
ORDER BY pct_of_all DESC;

-- 6. Hech qachon yiqilmagan student lar
SELECT first_name FROM students s
WHERE NOT EXISTS (SELECT 1 FROM enrollments e
                  WHERE e.student_id = s.student_id AND e.status = 'failed');

-- 7. 1 dan 12 gacha sonlar (rekursiv)
WITH RECURSIVE n(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM n WHERE x < 12)
SELECT x FROM n;

-- 8. Qayta yozish mashqi: #1 ni subquery emas, CTE ko'rinishida ifodalang
WITH stats AS (SELECT AVG(price) AS avg_price FROM courses)
SELECT c.title, c.price FROM courses c, stats WHERE c.price > stats.avg_price;

1-natija

8 qator

2-natija

1 qator

3-natija

… 74 qatordan birinchi 15 tasi

74 qator

4-natija

1 qator

5-natija

14 qator

6-natija

… 160 qatordan birinchi 15 tasi

160 qator

7-natija

12 qator

8-natija

8 qator

37 ms

Intervyu savollari

S: Correlated subquery nima?

Tashqi query ning column iga murojaat qiladigan subquery; shuning uchun u har bir tashqi row uchun hisoblanadi va alohida ishlay olmaydi. U ifodali, lekin O(n) marta bajarilishi mumkin; JOIN + GROUP BY odatda yaxshiroq masshtablanadi.

S: CTE va subquery — unumdorlikda farq bormi?

Odatda yo'q: ko'p engine lar CTE ni derived table kabi inline qiladi. Farq — o'qishlilik va qayta ishlatish. Ikkita istisno: PostgreSQL 12 dan oldin CTE larni doim materializatsiya qilardi (optimizatsiya to'sig'i), va bir necha marta murojaat qilingan CTE engine materializatsiya qilmasa bir necha marta hisoblanishi mumkin.

S: Qachon subquery emas, aynan CTE ishlatish shart?

Rekursiya uchun, va bir xil oraliq natija bitta query ichida bir martadan ko'p kerak bo'lganda.

S: Recursive CTE ning ikkita qismi qaysilar?

Anchor member (boshlang'ich row lar) va CTE ga murojaat qiladigan rekursiv member, ular UNION ALL bilan birlashtiriladi, ustiga rekursiv member ni oxir-oqibat bo'sh qiladigan shart qo'shiladi.

S: "Kamida bitta tugatilgan course i bor student lar" uchun IN, EXISTS yoki JOIN?

EXISTS eng mos: u birinchi mos kelganda to'xtaydi va har bir student ni bir marta qaytaradi. JOIN student ni har bir tugatilgan course uchun bir marta qaytaradi (fan-out) va DISTINCT talab qiladi. IN bu yerda yaxshi, lekin inkor qilingan zahoti xavfli bo'lib qoladi.

S: SELECT ichidagi subquery bir nechta row qaytara oladimi?

Yo'q — u scalar (bitta row, bitta column) bo'lishi kerak, aks holda query bajarilish paytida xato beradi. Nol row qaytarish mumkin, u NULL beradi.

S: Oylik hisobotda data si yo'q oylarni qanday ko'rsatasiz?

To'liq oy qatorini yarating (recursive CTE yoki calendar table), keyin data ni unga LEFT JOIN qiling va data tomonidagi column ni COUNT qiling.

Xulosa

• Subquery — boshqa ifoda ichidagi SELECT. Uning o'rni kerakli shaklini belgilaydi: SELECT/= da scalar, IN uchun qiymatlar column i, FROM da butun table.
• Scalar subquery har bir row yoniga umumiy xulosani qo'yadi. Derived table aggregate ustidan aggregate qilish va alias bo'yicha filtrlash imkonini beradi.
• Correlated subquery lar tashqi query ga murojaat qiladi va har bir row uchun ishlaydi — kichik data da tushunarli, kattasida sekin; masshtabda JOIN + GROUP BY ni afzal ko'ring.
• EXISTS/NOT EXISTS "kamida bittasi bormi?" ga javob beradi, erta to'xtaydi, fan-out qilmaydi va NULL ga xavfsiz.
• CTE (WITH) har bir qadamga nom beradi. Ularni vergul bilan zanjirlab, ulkan query ni o'qishli pipeline ga aylantiring.
• Recursive CTE (anchor + rekursiv qadam + to'xtash sharti) qator yaratadi va iyerarxiya bo'ylab yuradi — hisobotlarda bo'sh davrlarni 0 qilib ko'rsatish uchun ishlatiladi.
• JOIN ko'p tomonda row larni takrorlaydi, EXISTS esa yo'q. Join dan keyin DISTINCT ga qo'l cho'zish odatda EXISTS kerak bo'lganini bildiradi.

Keyingi: 7-bob — window function lar, ular row laringizni yo'qotmasdan aggregate qiymat beradi. Bu — analytics intervyusidagi eng qimmatli SQL ko'nikmasi.