SQL pastki so'rovlar (Subquery)

Subquery — boshqa so'rov ichidagi so'rov.

WHERE ichida subquery

-- O'rta balldan yuqori talabalar
SELECT ism, ball
FROM talabalar
WHERE ball > (SELECT AVG(ball) FROM talabalar);

-- Eng qimmat kursga yozilgan talabalar
SELECT ism
FROM talabalar
WHERE kurs_id = (
    SELECT id FROM kurslar
    ORDER BY narx DESC
    LIMIT 1
);

IN bilan subquery

-- Hech bo'lmaganda bitta kursga yozilgan talabalar
SELECT ism FROM talabalar
WHERE id IN (
    SELECT DISTINCT talaba_id FROM yozilishlar
);

-- Hech qanday kursga yozilmagan talabalar
SELECT ism FROM talabalar
WHERE id NOT IN (
    SELECT DISTINCT talaba_id FROM yozilishlar
    WHERE talaba_id IS NOT NULL
);

EXISTS

-- Yozilishi bor talabalar
SELECT t.ism
FROM talabalar t
WHERE EXISTS (
    SELECT 1 FROM yozilishlar y
    WHERE y.talaba_id = t.id
);

FROM ichida subquery (Derived Table)

-- Har shahar uchun eng yuqori ball
SELECT shahar, max_ball
FROM (
    SELECT shahar, MAX(ball) AS max_ball
    FROM talabalar
    GROUP BY shahar
) AS shahar_max
WHERE max_ball > 85;

SELECT ichida subquery (Scalar Subquery)

SELECT
    ism,
    ball,
    (SELECT AVG(ball) FROM talabalar) AS o'rta_ball,
    ball - (SELECT AVG(ball) FROM talabalar) AS farq
FROM talabalar;

CTE (Common Table Expression)

WITH yuqori_ballilar AS (
    SELECT ism, ball, shahar
    FROM talabalar
    WHERE ball > 80
),
toshkentliklar AS (
    SELECT * FROM yuqori_ballilar
    WHERE shahar = 'Toshkent'
)
SELECT * FROM toshkentliklar
ORDER BY ball DESC;