15-dars. Subqueries

Subquery nima?

Subquery — boshqa SQL query ichida yozilgan query.

Oddiy qilib:

Query ichidagi query — Subquery.

Masalan, biz quyidagi savolni olaylik:

O‘rtacha savdodan yuqori bo‘lgan orderlarni toping.

Birinchi navbatda o‘rtacha savdoni topishimiz kerak:

SELECT AVG(Sales)
FROM Sales.Orders;

Keyin shu natijadan foydalanib orderlarni topamiz:

SELECT * FROM Sales.Orders
WHERE Sales > 924;

Lekin bu ikki query'ni bitta query ichida yozishimiz mumkin:

SELECT * FROM Sales.Orders
WHERE Sales > (
    SELECT AVG(Sales)
    FROM Sales.Orders);

Mana:

(
    SELECT AVG(Sales)
    FROM Sales.Orders
)

— Subquery.

Subquery nima uchun kerak?

Subquery odatda bir query natijasidan foydalanib, boshqa query'da qo‘shimcha shart yoki hisob-kitob qilish kerak bo‘lganda ishlatiladi.

Masalan:

• o‘rtacha qiymatdan katta ma'lumotlarni topish;

• eng katta qiymatga ega qatorlarni topish;

• eng kichik qiymatga ega qatorlarni topish;

• ma'lum bir guruhga tegishli ma'lumotlarni topish;

• bir query natijasini boshqa query'ga berish.

Juda sodda fikr:

1-query
   ↓
natija
   ↓
2-query shu natijadan foydalanadi

Subquery qanday ishlaydi?

Masalan:

SELECT * FROM Sales.Orders
WHERE Sales > (
    SELECT AVG(Sales)
    FROM Sales.Orders);

SQL avval ichkaridagi query'ni bajaradi:

SELECT AVG(Sales)
FROM Sales.Orders;

Masalan natija:

924

Keyin asosiy query quyidagiga aylanadi:

SELECT * FROM Sales.Orders
WHERE Sales > 924;

Ya'ni:

Avval Subquery ishlaydi → natija olinadi → asosiy query shu natijadan foydalanadi.

Subquery qayerda yoziladi?

Biz Subquery'ni uchta joyda yozilishini o'rgansak yetarli:

Subquery
   ├── WHERE
   ├── FROM
   └── SELECT

WHERE ichidagi Subquery

Shart:

O‘rtacha savdodan yuqori bo‘lgan orderlarni toping.

SELECT
    OrderID,
    CustomerID,
    Sales
FROM Sales.Orders
WHERE Sales > (
    SELECT AVG(Sales)
    FROM Sales.Orders);

Bu yerda:

SELECT AVG(Sales)
FROM Sales.Orders

o‘rtacha Sales'ni topadi.

Asosiy query esa:

WHERE Sales > ...

orqali undan yuqori orderlarni topadi.

FROM ichidagi Subquery

Subquery FROM ichida ham yozilishi mumkin.

Masalan:

Har bir customer bo‘yicha jami savdoni hisoblab, natijani jadval sifatida ishlatish.

SELECT *
FROM (
    SELECT
        CustomerID,
        SUM(Sales) AS TotalSales
    FROM Sales.Orders
    GROUP BY CustomerID
) AS CustomerSales;

Bu yerda:

(
    SELECT
        CustomerID,
        SUM(Sales) AS TotalSales
    FROM Sales.Orders
    GROUP BY CustomerID
)

avval ishlaydi.

Uning natijasi esa asosiy query uchun vaqtinchalik jadvalga o‘xshab ishlaydi.

Bu yerda AS CustomerSales — unga nom berish.

SELECT ichidagi Subquery

Subquery SELECT ichida ham bo‘lishi mumkin.

Masalan:

Har bir orderni chiqarib, yonida umumiy o‘rtacha Sales'ni ham ko‘rsatish.

SELECT
    OrderID,
    Sales,
    (
        SELECT AVG(Sales)
        FROM Sales.Orders
    ) AS AverageSales
FROM Sales.Orders;

Natijada:

Bu yerda Subquery bitta qiymat — o‘rtacha savdoni qaytaryapti va u har bir qator yonida ko‘rsatilmoqda.

Subquery natijasiga ko‘ra turlari

Subquery'lar natija turiga qarab ham ajratilgan.

Subquery
   ├── Scalar Subquery
   ├── Row Subquery
   └── Table Subquery

Scalar Subquery

Bitta qiymat qaytaradi.

Masalan:

SELECT AVG(Sales)
FROM Sales.Orders;

Natija:

924

Shuning uchun bunday Subquery'ni:

WHERE Sales > (
    SELECT AVG(Sales)
    FROM Sales.Orders)

kabi ishlatishimiz mumkin.

Eslab qolish:

Scalar → bitta qiymat

Row Subquery

Bitta qator qaytaradigan Subquery.

Masalan, bir nechta ustun:

SELECT
    CustomerID,
    CustomerName
FROM Sales.Customers
WHERE CustomerID = 5;

Natija bitta customer bo‘lishi mumkin:

5 | Sardor

Bu — bitta qator.

Table Subquery

Bir nechta qator va ustunlardan iborat natija qaytaradi.

Masalan:

SELECT
    CustomerID,
    SUM(Sales) AS TotalSales
FROM Sales.Orders
GROUP BY CustomerID;

Natija:

Bunday natijani FROM ichida ishlatish mumkin:

SELECT *
FROM (
    SELECT
        CustomerID,
        SUM(Sales) AS TotalSales
    FROM Sales.Orders
    GROUP BY CustomerID
) AS CustomerSales;

Eslab qolish:

Scalar → bitta qiymat
Row    → bitta qator
Table  → jadvalga o‘xshash natija

Subquery'larning bog‘liqligi

Subquery
   ├── Non-Correlated
   └── Correlated

Non-Correlated Subquery

Subquery asosiy query'dan mustaqil ishlaydi.

Masalan:

SELECT *
FROM Sales.Orders
WHERE Sales > (
    SELECT AVG(Sales)
    FROM Sales.Orders);

Ichkaridagi:

SELECT AVG(Sales)
FROM Sales.Orders

asosiy query'dagi hech qanday ustunga murojaat qilmayapti.

Demak:

Non-Correlated Subquery — asosiy query'dan mustaqil ishlaydi.

Correlated Subquery

Bu yerda Subquery asosiy query'dagi qiymatga bog‘liq bo‘ladi. Ya'ni Subquery asosiy query'dagi qator bilan aloqa qiladi.

Soddaroq qilib:

Asosiy query
     ↓
Subquery
     ↓
asosiy query'dagi qatorni ishlatadi

Masalan, har bir customerning o‘rtacha savdosidan yuqori orderlarini tekshirish kabi vazifalarda correlated subquery ishlatilishi mumkin.

Lekin bu darsda correlated subquery'ni amaliy o‘rgatmaslikni tavsiya qilaman.

Shunchaki:

"Subquery'lar asosiy query'dan mustaqil yoki unga bog‘liq bo‘lishi mumkin. Bog‘liq Subquery Correlated Subquery deyiladi. Hozircha buni chuqur o‘rganmaymiz."

desangiz yetarli.

Subquery va JOIN farqi

Masalan:

USA customerlarning orderlarini topish.

Buni JOIN bilan ham yozish mumkin:

SELECT
    o.*
FROM Sales.Orders AS o
JOIN Sales.Customers AS c
    ON o.CustomerID = c.CustomerID
WHERE c.Country = 'USA';

Subquery bilan:

SELECT *
FROM Sales.Orders
WHERE CustomerID IN (
    SELECT CustomerID
    FROM Sales.Customers
    WHERE Country = 'USA');

Ikkalasi ham ishlashi mumkin.

JOIN — jadvallardagi ma'lumotlarni birlashtirish uchun.

Subquery — bir query natijasidan boshqa query ichida foydalanish uchun.

Qaysi holatda qaysi biri yaxshiroq degan savolni keyinchalik amaliyotda ko‘rib chiqamiz.

Subquery'ni qanday o‘qish kerak?

Masalan:

SELECT *
FROM Sales.Orders
WHERE Sales > (
    SELECT AVG(Sales)
    FROM Sales.Orders
);

Query'ni ichidan tashqariga qarab o‘qishni o‘rganamiz:

1-qadam

SELECT AVG(Sales)
FROM Sales.Orders

O‘rtacha Sales'ni top.

2-qadam

WHERE Sales > natija

O‘rtachadan katta orderlarni tanla.

3-qadam

SELECT *
FROM Sales.Orders

Kerakli orderlarni chiqar.

Darsga tegishli SQL fayllar

⬇️ Darsda ishlatilgan .sql fayli: 📎 dars_15.sql (2.4 KB)

⬇️ Uyga vazifa .sql fayli: 📎 dars_15_uy_ishi.sql (2.4 KB)