49 / 100

SUBQUERY

Quyi so'rovlar

Kirish: Ushbu qo'llanmada siz yanada moslashuvchan va sodda so'rovlar yozish uchun PostgreSQL quyi so'rovlaridan (subquery) qanday foydalanishni o'rganasiz.

PostgreSQL quyi so'rovlariga kirish

PostgreSQL bir so'rov ichida boshqa bir so'rovni joylashtirish (ichma-ich yozish) imkonini beradi. Ushbu ichki so'rov quyi so'rov (subquery), uni o'z ichiga olgan asosiy so'rov esa tashqi so'rov (outer query) deb ataladi.

Quyi so'rovni qayerlarda ishlatish mumkinligini tushunish uchun SELECT buyrug'ining asosiy tuzilishiga qaraylik:

query.sql

SELECT
  select_list
FROM
  table1
  JOIN table2 ON join_condition
WHERE
  condition;

Ushbu SELECT tuzilmasida:

SELECT bandi bitta qiymatni (ifoda yoki ustun) qabul qilishi mumkin.
FROM va JOIN bandlari jadvallarni yoki qatorlar va ustunlardan iborat natijalar to'plamini qabul qiladi.
WHERE bandi shartdagi operatorga qarab yagona qiymatni yoki qiymatlar ro'yxatini qabul qilishi mumkin.

Har bir band qanday turdagi ma'lumotni kutishiga qarab, mos quyi so'rovdan foydalaniladi.

WHERE bandida quyi so'rov

Faraz qilaylik, products jadvalidan eng qimmat mahsulotni topmoqchisiz. Buni ikki bosqichli so'rov bilan amalga oshirish mumkin:

Birinchi navbatda eng yuqori narxni aniqlaymiz:

query.sql

SELECT
  MAX(price)
FROM
  products;

Natija bitta son qaytaradi:

matn

   max
---------
 2999.99

Ikkinchi navbatda narxi shu maksimal narxga teng mahsulotni tanlaymiz:

query.sql

SELECT
  product_name,
  price
FROM
  products
WHERE
  price = 2999.99;

Natija:

matn

      product_name       |  price
-------------------------+---------
 Samsung QN900C Neo QLED | 2999.99

WHERE shartidagi narx bitta son qabul qilgani uchun, birinchi so'rovni ikkinchisining ichiga quyi so'rov sifatida joylashtirishimiz mumkin:

query.sql

SELECT
  product_name,
  price
FROM
  products
WHERE
  price = (
    SELECT
      MAX(price)
    FROM
      products
  );

Natija:

matn

      product_name       |  price
-------------------------+---------
 Samsung QN900C Neo QLED | 2999.99

Xuddi shunday, o'rtacha narxdan yuqori bo'lgan mahsulotlarni topish:

query.sql

SELECT
  product_name,
  price
FROM
  products
WHERE
  price > (
    SELECT
      AVG(price)
    FROM
      products
  );

SELECT bandida quyi so'rov

SELECT bandida yakka qiymat (skalyar) qaytaruvchi quyi so'rovdan foydalanish mumkin.

Masalan, quyidagi so'rov brand_id = 2 bo'lgan mahsulotlar nomi, narxi va ushbu brendning maksimal narxini yonma-yon chiqaradi:

query.sql

SELECT
  product_name,
  price,
  (
    SELECT
      MAX(price)
    FROM
      products
    WHERE
      brand_id = 2
  ) AS max_price
FROM
  products
WHERE
  brand_id = 2;

Natija:

matn

      product_name       |  price  | max_price
-------------------------+---------+-----------
 Apple iPhone 15         | 1099.99 |   1299.99
 Apple iPhone 15 Pro Max | 1299.99 |   1299.99
 Apple iPad Pro 12.9     | 1099.99 |   1299.99
 Apple AirPods Pro 3     |  249.99 |   1299.99
 Apple Watch Series 9    |  399.99 |   1299.99
 Apple iMac 24"          | 1299.99 |   1299.99

Quyidagi misol mahsulot nomi va uning ombordagi umumiy sonini quyi so'rov orqali hisoblab chiqaradi:

query.sql

SELECT
  p.product_name,
  (
    SELECT
      SUM(quantity)
    FROM
      inventories
    WHERE
      product_id = p.product_id
  ) AS inventory_qty
FROM
  products p
ORDER BY
  product_name;

FROM bandida quyi so'rov

FROM bandida jadval o'rnida qatorlar va ustunlar to'plamini qaytaruvchi quyi so'rovdan foydalanish mumkin.

Masalan, quyidagi so'rov barcha brendlar bo'yicha mahsulotlarning o'rtacha sonini hisoblaydi:

query.sql

SELECT
  AVG(product_count)
FROM
  (
    SELECT
      brand_id,
      COUNT(*) AS product_count
    FROM
      products
    GROUP BY
      brand_id
  );

Natija:

matn

        avg
--------------------
 2.5000000000000000

Ichki quyi so'rov har bir brend bo'yicha mahsulotlar sonini jamlaydi, tashqi so'rov esa hosil bo'lgan jadval asosida AVG() ni hisoblaydi.

JOIN bandida quyi so'rov

JOIN bandida ham butun boshli virtual jadval qaytaruvchi quyi so'rovdan foydalanish mumkin.

Masalan, ombordagi soni 300 dan oshiq bo'lgan mahsulotlarni birlashtirib olish:

query.sql

SELECT
  product_name,
  quantity
FROM
  products
  JOIN (
    SELECT
      product_id,
      quantity
    FROM
      inventories
    WHERE
      quantity > 300
  ) USING (product_id);

Natija:

matn

       product_name        | quantity
---------------------------+----------
 Lenovo ThinkPad X1 Carbon |      310
 Apple iMac 24"            |      320
 Dell Inspiron 27          |      330

Xulosa

Quyi so'rov (subquery) — boshqa bir so'rovning ichida joylashgan ichki so'rovdir.
Quyi so'rovni o'z ichiga olgan asosiy so'rov tashqi so'rov (outer query) deb ataladi.
Quyi so'rovlarni SELECT, FROM, JOIN va WHERE bandlarida mos ravishda ishlatish mumkin.

Oldingi

Keyingi