Ichki so'rovlar (subquery) - SQL va MySQL

Ichki so'rov - boshqa so'rov ichidagi SELECT. U murakkab savollarni bosqichma-bosqich yechish imkonini beradi.

Skalyar ichki so'rov #

Bitta qiymat qaytaradi:

SQL

-- O'rtachadan yuqori baholi talabalar
SELECT ism, familiya, ortacha_baho
FROM talabalar
WHERE ortacha_baho > (SELECT AVG(ortacha_baho) FROM talabalar);

Natija

+----------+-----------+--------------+
| ism      | familiya  | ortacha_baho |
+----------+-----------+--------------+
| Husanboy | Qodirov   |         4.60 |
| Malika   | Yusupova  |         4.90 |
| Nodira   | Sobirova  |         4.30 |
| Kamola   | Ergasheva |         4.75 |
+----------+-----------+--------------+

SELECT ichida ham ishlatish mumkin:

SQL

SELECT
    ism,
    ortacha_baho,
    (SELECT AVG(ortacha_baho) FROM talabalar)                AS umumiy_ortacha,
    ROUND(ortacha_baho - (SELECT AVG(ortacha_baho) FROM talabalar), 2) AS farq
FROM talabalar
ORDER BY farq DESC;

Ro'yxat qaytaradigan ichki so'rov #

SQL

-- 2026-yilgi guruhlardagi talabalar
SELECT ism, familiya
FROM talabalar
WHERE guruh_id IN (SELECT id FROM guruhlar WHERE yil = 2026);

-- Hech qachon buyurtma bermaganlar
SELECT ism, email
FROM mijozlar
WHERE id NOT IN (SELECT DISTINCT mijoz_id FROM buyurtmalar);

NOT IN va NULL - klassik tuzoq

SQL

SELECT * FROM mijozlar
WHERE id NOT IN (SELECT mijoz_id FROM buyurtmalar);

Agar ichki so'rov natijasida bitta NULL bo'lsa, butun so'rov hech narsa qaytarmaydi.

Sabab: id NOT IN (1, 2, NULL) = id <> 1 AND id <> 2 AND id <> NULL. Oxirgi shart hech qachon TRUE bo'lmaydi.

Yechimlar:

SQL

-- 1. NULL larni chiqarib tashlash
WHERE id NOT IN (SELECT mijoz_id FROM buyurtmalar WHERE mijoz_id IS NOT NULL)

-- 2. NOT EXISTS ishlatish (afzalroq)
WHERE NOT EXISTS (SELECT 1 FROM buyurtmalar b WHERE b.mijoz_id = mijozlar.id)

Bog'langan ichki so'rov #

Ichki so'rov tashqi so'rovning har bir qatoriga murojaat qiladi:

SQL

-- Har bir talabaning fanlari soni
SELECT
    t.ism,
    (SELECT COUNT(*) FROM talaba_fan tf WHERE tf.talaba_id = t.id) AS fanlar_soni
FROM talabalar t;

-- O'z guruhidagi o'rtachadan yuqori baholilar
SELECT t.ism, t.guruh_id, t.ortacha_baho
FROM talabalar t
WHERE t.ortacha_baho > (
    SELECT AVG(t2.ortacha_baho)
    FROM talabalar t2
    WHERE t2.guruh_id = t.guruh_id
);

Bog'langan ichki so'rovlar sekin bo'lishi mumkin

Ular tashqi so'rovning har bir qatori uchun qayta bajariladi. 1000 ta qator = 1000 ta ichki so'rov.

Ko'p hollarda JOIN bilan qayta yozish ancha tezroq:

SQL

-- Sekin
SELECT t.ism, (SELECT COUNT(*) FROM talaba_fan tf WHERE tf.talaba_id = t.id) AS soni
FROM talabalar t;

-- Tez
SELECT t.ism, COUNT(tf.fan_id) AS soni
FROM talabalar t
LEFT JOIN talaba_fan tf ON tf.talaba_id = t.id
GROUP BY t.id, t.ism;

`EXISTS` #

"Kamida bitta mos qator bormi?" degan savolga javob beradi:

SQL

-- Kamida bitta buyurtmasi bor mijozlar
SELECT m.ism, m.email
FROM mijozlar m
WHERE EXISTS (
    SELECT 1 FROM buyurtmalar b WHERE b.mijoz_id = m.id
);

-- Hech qanday buyurtmasi yo'qlar
SELECT m.ism
FROM mijozlar m
WHERE NOT EXISTS (
    SELECT 1 FROM buyurtmalar b WHERE b.mijoz_id = m.id
);

EXISTS ichida SELECT 1 yozing

SQL

WHERE EXISTS (SELECT 1 FROM ...)     -- to'g'ri
WHERE EXISTS (SELECT * FROM ...)     -- ishlaydi, lekin ortiqcha

EXISTS faqat qator bor-yo'qligini tekshiradi, qiymatlarni o'qimaydi. SELECT 1 niyatni aniq bildiradi.

Bundan tashqari EXISTS birinchi mos qatorni topgach to'xtaydi - shuning uchun IN dan tezroq ishlaydi.

`ANY` va `ALL` #

SQL

-- IT-101 guruhidagi ISTALGAN talabadan yuqori baho
SELECT ism, ortacha_baho FROM talabalar
WHERE ortacha_baho > ANY (
    SELECT ortacha_baho FROM talabalar WHERE guruh_id = 1
);

-- IT-101 dagi BARCHA talabalardan yuqori baho
SELECT ism, ortacha_baho FROM talabalar
WHERE ortacha_baho > ALL (
    SELECT ortacha_baho FROM talabalar WHERE guruh_id = 1
);

Operator	Ma'nosi
`> ANY (...)`	Eng kichigidan katta
`> ALL (...)`	Eng kattasidan katta
`< ANY (...)`	Eng kattasidan kichik
`= ANY (...)`	`IN` bilan bir xil

`FROM` ichidagi ichki so'rov #

Vaqtinchalik jadval sifatida:

SQL

-- Guruhlar bo'yicha statistika, keyin filtrlash
SELECT *
FROM (
    SELECT
        g.nomi,
        COUNT(t.id)                   AS talabalar,
        ROUND(AVG(t.ortacha_baho), 2) AS ortacha
    FROM guruhlar g
    LEFT JOIN talabalar t ON t.guruh_id = g.id
    GROUP BY g.id, g.nomi
) AS statistika
WHERE talabalar > 1
ORDER BY ortacha DESC;

FROM ichidagi ichki so'rovga taxallus majburiy

SQL

SELECT * FROM (SELECT ...) ;          -- XATO
SELECT * FROM (SELECT ...) AS t;      -- TO'G'RI

Natija

ERROR 1248 (42000): Every derived table must have its own alias

CTE - `WITH` ifodasi #

MySQL 8.0 va MariaDB 10.2 dan boshlab ichki so'rovlarni oldindan nomlash mumkin:

SQL

WITH guruh_statistikasi AS (
    SELECT
        g.id,
        g.nomi,
        COUNT(t.id)                   AS talabalar,
        ROUND(AVG(t.ortacha_baho), 2) AS ortacha
    FROM guruhlar g
    LEFT JOIN talabalar t ON t.guruh_id = g.id
    GROUP BY g.id, g.nomi
)
SELECT nomi, talabalar, ortacha
FROM guruh_statistikasi
WHERE talabalar > 1
ORDER BY ortacha DESC;

Bir nechta CTE:

SQL

WITH
faol_talabalar AS (
    SELECT * FROM talabalar WHERE faolmi = TRUE
),
alochilar AS (
    SELECT * FROM faol_talabalar WHERE ortacha_baho >= 4.5
)
SELECT
    (SELECT COUNT(*) FROM faol_talabalar) AS faollar,
    (SELECT COUNT(*) FROM alochilar)      AS alochilar;

CTE nima uchun afzal?

Afzallik	Izoh
O'qish oson	Murakkab so'rov nomlangan bosqichlarga bo'linadi
Qayta ishlatish	Bitta CTE ni bir necha marta ishlatish mumkin
Ichma-ichlik yo'q	3 daraja ichki so'rov o'rniga 3 ta ketma-ket CTE
Rekursiv	Ierarxiya bilan ishlash imkoniyati

Uzun so'rov yozayotgan bo'lsangiz, uni CTE larga bo'lib ko'ring - kod ancha tushunarli bo'ladi.

Rekursiv CTE #

Ierarxiyani aylanish uchun:

SQL

WITH RECURSIVE ierarxiya AS (
    -- Bazaviy holat: eng yuqori rahbar
    SELECT id, ism, lavozim, rahbar_id, 1 AS daraja
    FROM xodimlar
    WHERE rahbar_id IS NULL

    UNION ALL

    -- Rekursiv qism: har bir xodimning bo'ysunuvchilari
    SELECT x.id, x.ism, x.lavozim, x.rahbar_id, i.daraja + 1
    FROM xodimlar x
    JOIN ierarxiya i ON i.id = x.rahbar_id
)
SELECT
    CONCAT(REPEAT('    ', daraja - 1), ism) AS tuzilma,
    lavozim,
    daraja
FROM ierarxiya
ORDER BY daraja, ism;

Natija

+---------------------------+-------------------+--------+
| tuzilma                   | lavozim           | daraja |
+---------------------------+-------------------+--------+
| Aziz Karimov              | Direktor          |      1 |
|     Dilnoza Yusupova      | Bo'lim boshlig'i  |      2 |
|         Malika Tosheva    | Dasturchi         |      3 |
|         Sardor Aliyev     | Dasturchi         |      3 |
+---------------------------+-------------------+--------+

Rekursiv CTE qayerda kerak?

Xodimlar ierarxiyasi
Ichma-ich kategoriyalar (kategoriya → ichki kategoriya → ...)
Izohlarga javoblar daraxti
Yo'nalishlar tarmog'i

Ilgari bu masalalar dasturlash tilida sikl bilan yechilardi.

`UPDATE` va `DELETE` da ichki so'rov #

SQL

-- Guruh statistikasini yangilash
UPDATE guruhlar g
SET talabalar_soni = (
    SELECT COUNT(*) FROM talabalar t WHERE t.guruh_id = g.id
);

-- Faol bo'lmagan guruhlardagi talabalarni o'chirish
DELETE FROM talabalar
WHERE guruh_id IN (SELECT id FROM guruhlar WHERE yil < 2024);

MySQL cheklovi

SQL

DELETE FROM talabalar
WHERE guruh_id IN (SELECT guruh_id FROM talabalar WHERE ...);

Natija

ERROR 1093 (HY000): You can't specify target table 'talabalar'
for update in FROM clause

MySQL bir jadvalni bir vaqtda o'qib va o'zgartira olmaydi. Yechim - qo'shimcha ichki so'rov bilan "o'rash":

SQL

DELETE FROM talabalar
WHERE id IN (
    SELECT * FROM (SELECT id FROM talabalar WHERE ...) AS vaqtinchalik
);

Ichki so'rov yoki `JOIN`? #

Vazifa	Tavsiya
Bitta agregat qiymat bilan solishtirish	Ichki so'rov
Ikkala jadvaldan ustun kerak	`JOIN`
"Bor-yo'qligini" tekshirish	`EXISTS`
"Yo'qlarni topish"	`LEFT JOIN + IS NULL` yoki `NOT EXISTS`
Bosqichma-bosqich hisoblash	CTE
Ierarxiya	Rekursiv CTE

Shubha bo'lsa - EXPLAIN bilan tekshiring

SQL

EXPLAIN SELECT ...;

U so'rov qanday bajarilishini ko'rsatadi. Ikki variantni yozib, qaysi biri kamroq qator o'qishini solishtiring. Buni 13-bo'limda batafsil ko'ramiz.

Amaliy misollar #

SQL

-- 1. Har bir shahardagi eng yuqori baholi talaba
SELECT t.ism, t.shahar, t.ortacha_baho
FROM talabalar t
WHERE t.ortacha_baho = (
    SELECT MAX(t2.ortacha_baho) FROM talabalar t2 WHERE t2.shahar = t.shahar
);

-- 2. O'rtacha chekdan yuqori buyurtmalar
WITH chek_summasi AS (
    SELECT b.id, SUM(be.soni * be.narx) AS summa
    FROM buyurtmalar b
    JOIN buyurtma_elementlari be ON be.buyurtma_id = b.id
    GROUP BY b.id
)
SELECT id, summa
FROM chek_summasi
WHERE summa > (SELECT AVG(summa) FROM chek_summasi)
ORDER BY summa DESC;

-- 3. Hech qachon sotilmagan mahsulotlar
SELECT nomi, narx
FROM mahsulotlar m
WHERE NOT EXISTS (
    SELECT 1 FROM buyurtma_elementlari be WHERE be.mahsulot_id = m.id
);

-- 4. Eng ko'p xarid qilgan 5 ta mijoz
WITH mijoz_xaridlari AS (
    SELECT
        m.id, m.ism,
        SUM(be.soni * be.narx) AS jami
    FROM mijozlar m
    JOIN buyurtmalar b           ON b.mijoz_id = m.id
    JOIN buyurtma_elementlari be ON be.buyurtma_id = b.id
    WHERE b.holat <> 'bekor'
    GROUP BY m.id, m.ism
)
SELECT ism, jami,
       ROUND(jami * 100.0 / (SELECT SUM(jami) FROM mijoz_xaridlari), 1) AS ulush_foiz
FROM mijoz_xaridlari
ORDER BY jami DESC
LIMIT 5;

Amaliy topshiriq

kutubxona bazasida:

O'rtacha narxdan qimmat kitoblarni toping.
IN bilan ma'lum mamlakatdagi mualliflarning kitoblarini chiqaring.
NOT EXISTS bilan hech qachon ijaraga olinmagan kitoblarni toping.
Bog'langan ichki so'rov bilan har bir kitob nechta marta olinganini ko'rsating.
Xuddi shu natijani JOIN + GROUP BY bilan oling va EXPLAIN bilan solishtiring.
CTE bilan janr statistikasini hisoblang, so'ng 3 tadan ko'p kitobi borlarini filtrlang.
Har bir janrdagi eng qimmat kitobni toping.
NOT IN va NULL tuzog'ini o'zingiz hosil qilib ko'ring.

Xulosa #

Skalyar ichki so'rov bitta qiymat, ro'yxatli esa qiymatlar to'plamini qaytaradi.
NOT IN + NULL - klassik tuzoq; NOT EXISTS xavfsizroq.
Bog'langan ichki so'rovlar har bir qator uchun qayta bajariladi - sekin bo'lishi mumkin.
EXISTS birinchi mos qatorda to'xtaydi - IN dan tezroq.
FROM ichidagi ichki so'rovga taxallus majburiy.
CTE (WITH) murakkab so'rovni nomlangan bosqichlarga bo'ladi.
Rekursiv CTE ierarxiyalar bilan ishlash uchun.
Ikki variant orasida tanlashda EXPLAIN bilan tekshiring.

Keyingi bo'limda indekslar va so'rov optimizatsiyasini o'rganamiz.

Skalyar ichki so'rov #

Ro'yxat qaytaradigan ichki so'rov #

Bog'langan ichki so'rov #

EXISTS #

ANY va ALL #

FROM ichidagi ichki so'rov #

CTE - WITH ifodasi #

Rekursiv CTE #

UPDATE va DELETE da ichki so'rov #

Ichki so'rov yoki JOIN? #

Amaliy misollar #

Xulosa #

Xatolik topdingizmi?

`EXISTS` #

`ANY` va `ALL` #

`FROM` ichidagi ichki so'rov #

CTE - `WITH` ifodasi #

`UPDATE` va `DELETE` da ichki so'rov #

Ichki so'rov yoki `JOIN`? #