Ichki so‘rovlar

Ichki so‘rov (subquery) — boshqa so‘rov ichiga qavs bilan yozilgan so‘rov. U avval bajariladi va natijasini tashqi so‘rovga beradi.

Bitta qiymat qaytaradigan ichki so‘rov

«O‘rtachadan yuqori maosh oladigan ustozlar» — o‘rtachani oldindan bilmaymiz, lekin baza hisoblab bera oladi:

SELECT first_name, last_name, salary
FROM teachers
WHERE salary > (SELECT AVG(salary) FROM teachers)
ORDER BY salary DESC;

Ichki so‘rov bitta son qaytaradi, tashqi so‘rov u bilan solishtiradi.

Ro‘yxat qaytaradigan ichki so‘rov: IN

«Kamida bitta 95+ baho olgan talabalar»:

SELECT first_name, last_name
FROM students
WHERE id IN (SELECT student_id FROM grades WHERE score >= 95);

Ichki so‘rov bir nechta qator qaytarsa, = emas, IN ishlating. = bilan baza «subquery returns more than one row» xatosini beradi.

EXISTS — «bormi?»

EXISTS ichki so‘rov kamida bitta qator qaytarsa, to‘g‘ri bo‘ladi. Ko‘pincha tashqi qatorga bog‘lanadi:

SELECT p.name
FROM products p
WHERE NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = p.id);

Hech qachon sotilmagan mahsulotlar. SELECT 1 — ichida nima qaytishi muhim emas, faqat bor-yo‘qligi.

Bog‘langan (correlated) ichki so‘rov

Ichki so‘rov tashqi qatorning qiymatidan foydalanadi va har bir qator uchun qaytadan bajariladi. «O‘z toifasidagi o‘rtacha narxdan qimmat mahsulotlar»:

SELECT p.name, p.price
FROM products p
WHERE p.price > (SELECT AVG(x.price) FROM products x WHERE x.category_id = p.category_id);

FROM ichida ichki so‘rov

Natijani vaqtinchalik jadvaldek ishlatish mumkin. Unga albatta taxallus beriladi:

SELECT AVG(n) AS avg_orders
FROM (SELECT customer_id, COUNT(*) AS n FROM orders GROUP BY customer_id) t;

Esda qoldiring

• Bitta qiymat — =, > bilan; ro‘yxat — IN bilan.
• EXISTS / NOT EXISTS — bor-yo‘qligini tekshiradi.
• FROM dagi ichki so‘rovga taxallus shart.