Subquery — WHERE, FROM, SELECT ichida

~20 daq · MySQL · Subquery va CTE

Muammodan boshlaylik

Savol: "O'rtacha narxdan qimmat mahsulotlar qaysilar?"

Ikki qadamda yechish mumkin:

SELECT AVG(narx) FROM mahsulotlar;            -- 6445714
SELECT nom, narx FROM mahsulotlar WHERE narx > 6445714;

Lekin ertaga yangi mahsulot qo'shilsa — o'rtacha o'zgaradi va ikkinchi so'rovdagi raqam eskiradi. Bu raqamni qo'lda ko'chirish emas, bir so'rovning natijasini ikkinchisiga to'g'ridan-to'g'ri berish kerak.

Subquery (ichki so'rov) — boshqa so'rov ichiga joylashtirilgan SELECT.

WHERE ichida — bitta qiymat

SELECT nom, narx
FROM mahsulotlar
WHERE narx > (SELECT AVG(narx) FROM mahsulotlar);

+----------------+----------+
| nom            | narx     |
+----------------+----------+
| iPhone 15      | 12500000 |
| MacBook Air M3 | 16900000 |
| Lenovo IdeaPad |  7200000 |
+----------------+----------+

MySQL avval qavs ichidagi so'rovni bajaradi (6445714.29), keyin natijani tashqi so'rovga qo'yadi.

Qavs ichidagi so'rov bitta qiymat (bitta qator, bitta ustun) qaytaradi — bu skalyar subquery. Uni oddiy son o'rnida istalgan joyda ishlatish mumkin.

Agar ko'p qator qaytsa:

SELECT nom FROM mahsulotlar WHERE narx = (SELECT narx FROM mahsulotlar WHERE toifa_id = 1);
-- ERROR 1242: Subquery returns more than 1 row

Eng qimmat mahsulot (bir xil narxli bir nechtasi bo'lsa — hammasi):

SELECT nom, narx FROM mahsulotlar
WHERE narx = (SELECT MAX(narx) FROM mahsulotlar);

WHERE ichida — ro'yxat (IN)

Subquery bir ustun va ko'p qator qaytarsa — IN bilan:

-- Kamida bir marta sotilgan mahsulotlar
SELECT nom FROM mahsulotlar
WHERE id IN (SELECT mahsulot_id FROM buyurtma_qatorlari);

-- Toshkentlik mijozlarning buyurtmalari
SELECT id, holat FROM buyurtmalar
WHERE mijoz_id IN (SELECT id FROM mijozlar WHERE shahar = 'Toshkent');

NOT IN va NULL tuzog'i

-- Hech qachon sotilmagan mahsulotlar
SELECT nom FROM mahsulotlar
WHERE id NOT IN (SELECT mahsulot_id FROM buyurtma_qatorlari);
-- Lenovo IdeaPad ✅

Ishlaydi. Lekin subquery natijasida bitta NULL bo'lsa:

SELECT 5 NOT IN (1, 2);         -- 1 (TRUE)
SELECT 5 NOT IN (1, 2, NULL);   -- NULL — TRUE emas!

-- Mahsuloti yo'q toifalar (bizda hammasida bor, lekin muammo boshqa joyda)
SELECT nom FROM toifalar
WHERE id NOT IN (SELECT toifa_id FROM mahsulotlar);
-- Empty set — chunki "Sovg'a kartasi" ning toifa_id si NULL

5 NOT IN (1, 2, NULL) → "5 ≠ 1 VA 5 ≠ 2 VA 5 ≠ NULL" → oxirgisi NULL → butun ifoda NULL → qator tashlanadi. Bitta NULL butun NOT IN ni bo'sh natijaga aylantiradi.

[!WARNING] NOT IN (subquery) ishlatsangiz, subquery ustunida NULL bo'lmasligiga ishonch hosil qiling (WHERE ustun IS NOT NULL qo'shing). Yoki xavfsiz alternativ — NOT EXISTS (22-dars) yoki LEFT JOIN ... IS NULL (16-dars).

FROM ichida — vaqtinchalik jadval

Subquery natijasi — jadval. Uni FROM da ham ishlatish mumkin. Bunga derived table deyiladi:

-- Mijozlar bo'yicha o'rtacha chek
SELECT ROUND(AVG(jami)) AS ortacha_chek
FROM (
    SELECT b.id, SUM(q.soni * q.narx) AS jami
    FROM buyurtmalar b
    JOIN buyurtma_qatorlari q ON q.buyurtma_id = b.id
    WHERE b.holat <> 'bekor'
    GROUP BY b.id
) AS cheklar;

Ikki bosqichli hisob:

1. Ichkarida — har bir buyurtmaning summasi
2. Tashqarida — shu summalarning o'rtachasi

Bitta GROUP BY bilan buni qilib bo'lmaydi — "aggregate ning aggregate i" kerak.

Derived table ga taxallus berish shart (AS cheklar):

SELECT * FROM (SELECT 1);
-- ERROR 1248: Every derived table must have its own alias

Guruhlangan natijani filtrlash va JOIN qilish

-- Har bir toifaning eng qimmat mahsuloti
SELECT p.nom, p.narx, p.toifa_id
FROM mahsulotlar p
JOIN (
    SELECT toifa_id, MAX(narx) AS max_narx
    FROM mahsulotlar
    WHERE toifa_id IS NOT NULL
    GROUP BY toifa_id
) AS eng ON eng.toifa_id = p.toifa_id AND eng.max_narx = p.narx;

+----------------+----------+----------+
| nom            | narx     | toifa_id |
+----------------+----------+----------+
| iPhone 15      | 12500000 |        1 |
| MacBook Air M3 | 16900000 |        2 |
| AirPods Pro    |  3100000 |        3 |
+----------------+----------+----------+

"Har bir guruhdagi eng ..." — klassik masala. Window function bilan yanada qulay (29-dars).

SELECT ichida — har bir qator uchun qiymat

SELECT
    nom,
    narx,
    narx - (SELECT AVG(narx) FROM mahsulotlar) AS ortachadan_farq
FROM mahsulotlar;

Bog'liq qiymat bilan (correlated — keyingi dars):

SELECT
    m.ism,
    (SELECT COUNT(*) FROM buyurtmalar b WHERE b.mijoz_id = m.id) AS buyurtmalar
FROM mijozlar m;

Bu LEFT JOIN + GROUP BY ning muqobili. Kichik jadvallarda qulay va o'qishli. Katta jadvallarda — har bir qator uchun alohida subquery bajarilishi mumkin, shuning uchun EXPLAIN bilan tekshiring (32-dars).

Subquery INSERT, UPDATE, DELETE da

-- Hech narsa sotib olmagan, 1 yildan eski mijozlarni arxivlash
INSERT INTO arxiv_mijozlar
SELECT * FROM mijozlar
WHERE yaratilgan < NOW() - INTERVAL 1 YEAR
  AND id NOT IN (SELECT mijoz_id FROM buyurtmalar);

-- O'rtachadan qimmat mahsulotlarga 5% chegirma
UPDATE mahsulotlar
SET narx = ROUND(narx * 0.95)
WHERE narx > (SELECT * FROM (SELECT AVG(narx) FROM mahsulotlar) AS t);

Oxirgi misoldagi g'alati (SELECT * FROM (...) AS t) — MySQL cheklovi: o'zgartirilayotgan jadvaldan to'g'ridan-to'g'ri subquery qilib bo'lmaydi:

ERROR 1093: You can't specify target table 'mahsulotlar' for update in FROM clause

Ichki so'rovni derived table ga o'rash MySQL ni natijani avval vaqtinchalik jadvalga yozishga majbur qiladi va muammo yo'qoladi.

Subquery yoki JOIN?

Ko'p masalani ikkala usulda yechish mumkin:

-- Subquery
SELECT nom FROM mahsulotlar
WHERE id IN (SELECT mahsulot_id FROM buyurtma_qatorlari);

-- JOIN
SELECT DISTINCT p.nom FROM mahsulotlar p
JOIN buyurtma_qatorlari q ON q.mahsulot_id = p.id;

Qoida: ikkinchi jadvaldan ustun kerak bo'lsa — JOIN. Faqat filtr sifatida kerak bo'lsa — subquery (IN / EXISTS) ko'pincha o'qishliroq. Shubha bo'lsa — EXPLAIN bilan solishtiring.

Nima uchun shunday?

Nima uchun SQL so'rov ichida so'rov yozishga ruxsat beradi?

Chunki SQL ning asosiy g'oyasi — har bir so'rov natijasi ham jadval. Jadval qayerda kerak bo'lsa (FROM, IN, JOIN), u yerga so'rov qo'yish mumkin. Bitta qiymat (1×1 jadval) kerak bo'lsa — skalyar subquery. Bu "kompozitsiya" — kichik, tushunarli so'rovlardan murakkab savollarni qurish imkonini beradi. Keyingi darslardagi CTE shu g'oyaning yanada o'qishli shakli.

Xulosa

Eslab qoling:

• Skalyar subquery — bitta qiymat: WHERE narx > (SELECT AVG(narx) ...)
• IN (subquery) — ro'yxat bo'yicha filtr
• NOT IN + NULL = bo'sh natija; NOT EXISTS yoki IS NOT NULL filtr
• FROM (subquery) AS t — derived table, taxallus majburiy
• "Aggregate ning aggregate i" va "har bir guruhdagi eng ..." — derived table bilan
• O'zgartirilayotgan jadvaldan subquery — derived table ga o'rang (1093 xatosi)
• Ustun kerak — JOIN, faqat filtr — subquery

Keyingi dars

22-dars: Correlated Subquery — EXISTS, NOT EXISTS