51 / 100

WHERE IN SUBQUERY

WHERE IN bilan quyi so'rovlar

Kirish: Ushbu qo'llanmada siz PostgreSQL WHERE IN quyi so'rovi yordamida ichki so'rov qaytargan qiymatlar ro'yxatiga asoslanib qatorlarni filtrlash, yangilash va o'chirishni o'rganasiz.

PostgreSQL WHERE IN quyi so'roviga kirish

PostgreSQLda WHERE IN quyi so'rovi ma'lum bir ustun qiymati ichki so'rov qaytargan qiymatlar ro'yxatida mavjud yoki yo'qligini tekshirish imkonini beradi.

SELECT buyrug'ida WHERE IN quyi so'rovini qo'llash sintaksisi:

query.sql

SELECT select_list
FROM table_name
WHERE column_name IN (subquery);

Ushbu sintaksisda:

WHERE bandida IN operatori bilan birga tekshirilayotgan ustun nomi ko'rsatiladi.
Qavslar (subquery) ichiga bir ustunli qiymatlar to'plamini qaytaruvchi quyi so'rov yoziladi.

Agar quyi so'rov hech qanday qator qaytarmasa, tashqi so'rov ham bo'sh natija beradi.

PostgreSQL WHERE IN quyi so'roviga misol

Quyidagi so'rov warehouse_id = 1 bo'lgan omborda saqlanayotgan barcha mahsulotlarning nomlarini topadi:

query.sql

SELECT
  product_name
FROM
  products
WHERE
  product_id IN (
    SELECT
      product_id
    FROM
      inventories
    WHERE
      warehouse_id = 1
  );

Natija:

matn

        product_name
----------------------------
 Xiaomi Mi 14
 Apple iPhone 15 Pro Max
 Apple AirPods Pro 3
 Samsung Galaxy Watch 6
 Samsung QN900C Neo QLED
 Bose SoundLink Max
 Microsoft Surface Laptop 5
 Dell Inspiron 27

Bunda:

Ichki quyi so'rov 1-raqamli ombordagi barcha product_id larni ro'yxat qilib oladi.
Tashqi so'rov esa products jadvalidan identifikatori shu ro'yxatda bor bo'lgan tovarlarni tanlaydi.

PostgreSQL WHERE NOT IN quyi so'rovi

Ro'yxatda yo'q bo'lgan ma'lumotlarni saralash uchun NOT IN operatoridan foydalanasiz.

Masalan, 1-raqamli omborda saqlanmayotgan barcha mahsulotlar:

query.sql

SELECT
  product_name
FROM
  products
WHERE
  product_id NOT IN (
    SELECT
      product_id
    FROM
      inventories
    WHERE
      warehouse_id = 1
  );

Natija:

matn

       product_name
---------------------------
 Sony Xperia 1 VI
 Samsung Galaxy Z Fold 5
 Samsung Galaxy Tab S9
 Apple iPad Pro 12.9
 Samsung Galaxy Buds Pro 2
 Apple Watch Series 9
 LG OLED TV C3
 Sony Bravia XR A95K
 LG G3 OLED
 Sony HT-A7000 Soundbar
 Dell XPS 15
 HP Spectre x360
 Lenovo ThinkPad X1 Carbon
 Apple iMac 24"

UPDATE buyrug'ida WHERE IN quyi so'rovi

UPDATE buyrug'ida qaysi qatorlarni o'zgartirish kerakligini aniqlash uchun ham WHERE IN quyi so'rovidan foydalanish mumkin:

query.sql

UPDATE table_name
SET column2 = value2
WHERE column1 IN (subquery);

Masalan, 1-raqamli ombordagi barcha tovarlarning narxini 10% ga arzonlashtiramiz:

query.sql

UPDATE products
SET
  price = price * 0.9
WHERE
  product_id IN (
    SELECT
      product_id
    FROM
      inventories
    WHERE
      warehouse_id = 1
  ) RETURNING *;

Natija:

matn

product_id |        product_name        |  price  | brand_id | category_id
------------+----------------------------+---------+----------+-------------
          4 | Xiaomi Mi 14               |  719.99 |        4 |           2
          7 | Apple iPhone 15 Pro Max    | 1169.99 |        2 |           3
         10 | Apple AirPods Pro 3        |  224.99 |        2 |           5
         13 | Samsung Galaxy Watch 6     |  314.99 |        1 |           6
         16 | Samsung QN900C Neo QLED    | 2699.99 |        1 |           8
         19 | Bose SoundLink Max         |  359.99 |        7 |           9
         22 | Microsoft Surface Laptop 5 | 1169.99 |        9 |          11
         25 | Dell Inspiron 27           |  899.99 |        7 |          12

DELETE buyrug'ida WHERE IN quyi so'rovi

O'chirilishi kerak bo'lgan qatorlarni dinamik aniqlash uchun DELETE da WHERE IN qo'llaniladi:

query.sql

DELETE FROM table_name
WHERE column1 IN (subquery);

Masalan, 1-raqamli ombordagi barcha tovarlarni products jadvalidan o'chirish:

query.sql

DELETE FROM products
WHERE
  product_id IN (
    SELECT
      product_id
    FROM
      inventories
    WHERE
      warehouse_id = 1
  ) 
RETURNING *;

Xulosa

Quyi so'rov qaytargan dinamik qiymatlar ro'yxati bo'yicha ma'lumotlarni saralash uchun WHERE IN (subquery) dan foydalaning.
Ro'yxatdan tashqaridagi qatorlarni tanlash uchun NOT IN operatorini qo'llang.
WHERE IN quyi so'rovlarini nafaqat SELECT, balki UPDATE va DELETE amallarida ham samarali qo'llash mumkin.

Oldingi

Keyingi