SQL Subquery (ichki so'rov) ishlatish

Subquery — boshqa so'rov ichidagi so'rov.

WHERE ichida subquery

-- O'rtachadan baland narxli mahsulotlar
SELECT nomi, narx
FROM mahsulotlar
WHERE narx > (SELECT AVG(narx) FROM mahsulotlar);

-- Eng ko'p buyurtma bergan mijoz
SELECT ism
FROM mijozlar
WHERE id = (
    SELECT mijoz_id
    FROM buyurtmalar
    GROUP BY mijoz_id
    ORDER BY COUNT(*) DESC
    LIMIT 1
);

IN bilan subquery

-- Kamida bitta buyurtma bergan mijozlar
SELECT ism
FROM mijozlar
WHERE id IN (SELECT DISTINCT mijoz_id FROM buyurtmalar);

-- Hech buyurtma bermagan mijozlar
SELECT ism
FROM mijozlar
WHERE id NOT IN (SELECT DISTINCT mijoz_id FROM buyurtmalar);

FROM ichida subquery (derived table)

SELECT shahar, ortacha_yosh
FROM (
    SELECT shahar, AVG(yosh) AS ortacha_yosh
    FROM talabalar
    GROUP BY shahar
) AS shahar_statistika
WHERE ortacha_yosh > 25;

CTE (Common Table Expression)

Subquery o'rniga yanada o'qimishli yozish:

WITH yuqori_talabalar AS (
    SELECT talaba_id, AVG(baho) AS ortacha
    FROM baholar
    GROUP BY talaba_id
    HAVING AVG(baho) >= 85
)
SELECT t.ism, yt.ortacha
FROM talabalar t
INNER JOIN yuqori_talabalar yt ON t.id = yt.talaba_id;