Subquery

Subquery — boshqa SQL statement ichida joylashgan ichki query. U scalar qiymat, row, columnlar to‘plami yoki derived table natijasini yaratishi mumkin.

Subquery SELECT, FROM, WHERE, HAVING, INSERT, UPDATE va DELETE ichida ishlatiladi.

Scalar subquery

Bitta row va bitta column qaytarishi kutiladi.

SELECT
    name,
    (SELECT MAX(created_at) FROM orders) AS last_order_time
FROM users;

Agar scalar subquery bir nechta row qaytarsa database xato beradi.

Hech row bo‘lmasa natija NULL bo‘lishi mumkin.

WHERE ichidagi subquery

Filter qiymati ichki query’dan olinadi.

SELECT *
FROM orders
WHERE user_id IN (
    SELECT id
    FROM users
    WHERE active = true
);

Optimizer buni semi join yoki boshqa planga aylantirishi mumkin.

IN

Subquery qaytargan qiymatlar to‘plamiga membership tekshiradi.

Kichik statik ro‘yxat va subquery uchun qulay.

NOT IN ichki natijada NULL bo‘lsa SQL uch qiymatli mantiqi sabab kutilmaganda hech row qaytarmasligi mumkin.

Anti join uchun NOT EXISTS ko‘pincha xavfsizroq.

EXISTS

Ichki query kamida bitta row qaytarsa true.

SELECT *
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);

Ichki projectiondagi 1 qiymatining o‘zi muhim emas.

Database birinchi mos row topilgach qidirishni to‘xtatishi mumkin.

NOT EXISTS

Mos child row bo‘lmagan parentlarni topadi.

SELECT *
FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);

Bu anti join semantikasi.

Null qiymatlar bilan NOT INga qaraganda aniqroq.

Correlated subquery

Ichki query tashqi query rowiga murojaat qiladi.

SELECT *
FROM products p
WHERE price > (
    SELECT AVG(p2.price)
    FROM products p2
    WHERE p2.category_id = p.category_id
);

Har product o‘z category o‘rtachasi bilan solishtiriladi.

Optimizer uni join va aggregationga qayta yozishi mumkin.

Uncorrelated subquery

Ichki query tashqi rowga bog‘liq emas.

U bir marta hisoblanishi yoki optimizer tomonidan alohida plan qilinishi mumkin.

Misol, barcha orderlarni global average bilan solishtirish.

Derived table

FROM ichidagi subquery vaqtinchalik tablega o‘xshash natija beradi.

SELECT x.category_id, x.total
FROM (
    SELECT category_id, SUM(amount) AS total
    FROM sales
    GROUP BY category_id
) AS x;

Derived table aliasga ega bo‘lishi kerak bo‘lishi mumkin.

Lateral subquery

Lateral subquery FROM ichida oldingi table rowlariga murojaat qiladi.

Bu har parent uchun top N child, computed set yoki parameterized function chaqirish uchun qulay.

Masalan, har userning eng so‘nggi uchta orderi.

Aniq syntax database’ga bog‘liq.

SELECT list subquery

Har outer row uchun bitta qiymat olish mumkin.

SELECT
    u.id,
    (
        SELECT COUNT(*)
        FROM orders o
        WHERE o.user_id = u.id
    ) AS order_count
FROM users u;

Katta datasetda aggregation join yoki precomputed count samaraliroq bo‘lishi mumkin.

UPDATE ichidagi subquery

Update qiymati yoki filter ichki query’dan olinadi.

UPDATE products
SET category_name = (
    SELECT name
    FROM categories
    WHERE categories.id = products.category_id
);

Scalar natija va null holati tekshiriladi.

Ko‘p database UPDATE ... FROMga o‘xshash join syntaxini ham beradi.

DELETE ichidagi subquery

Bog‘liq bo‘lmagan yoki eskirgan rowlarni ichki query orqali topish mumkin.

DELETE FROM sessions
WHERE user_id IN (
    SELECT id FROM users WHERE deleted_at IS NOT NULL
);

Katta delete batch, lock va WAL hajmi bo‘yicha boshqariladi.

Subquery va join

Ko‘p subquery join bilan qayta yozilishi mumkin.

Lekin semantika bir xil bo‘lishi shart emas.

Masalan, child mavjudligini tekshirish uchun join duplicate parent rowlar yaratishi mumkin.

EXISTS kerakli ma’noni aniq ifodalaydi.

Subquery flattening

Optimizer ichki queryni outer query bilan birlashtirishi mumkin.

Bu predicate pushdown va join reorder imkonini beradi.

LIMIT, aggregation, window function yoki volatile expression optimization barrier bo‘lishi mumkin.

Materialization

Database subquery natijasini vaqtinchalik saqlab, keyin qayta ishlatishi mumkin.

Materialization:

• qayta hisoblashni kamaytiradi;
• memory yoki temp disk ishlatadi;
• tashqi filter pushdownini cheklashi mumkin.

Execution plan haqiqiy tanlovni ko‘rsatadi.

Performance

Correlated subquery har outer row uchun ishlayotgandek ko‘rinishi mumkin.

Optimizer decorrelation qilmasa N marta execution yuz beradi.

Index, row soni, aggregation va cache performance’ga ta’sir qiladi.

EXPLAIN orqali loop soni tekshiriladi.

Readability

Juda chuqur nested subquery queryni tushunishni qiyinlashtiradi.

Nomlangan CTE:

• bosqichlarni ajratish;
• qayta ishlatish;
• debugging;
• business ma’noli alias

uchun ishlatilishi mumkin.

Biroq CTE performance semantikasi database versiyasiga bog‘liq.

Quantified comparison

Subquery natijasi ANY, SOME yoki ALLga o‘xshash operatorlar bilan solishtirilishi mumkin.

Masalan, qiymat barcha qaytgan qiymatlardan katta yoki kamida bittasiga tengligini tekshirish.

Bo‘sh natija va NULL semantikasi alohida hisobga olinadi.

Scalar cache

Uncorrelated scalar subquery optimizer tomonidan bir marta hisoblanishi mumkin.

Volatile function yoki tashqi dependency mavjud bo‘lsa qayta execution semantikasi o‘zgaradi.

Execution plan subquery necha marta ishlaganini ko‘rsatadi.

Bog‘liq tushunchalar

Nested query, Scalar subquery, Correlated subquery, EXISTS, IN, Derived table, Lateral join, Query optimizer, CTE, Semi join