52 / 100

EXISTS

Mavjudlikni tekshirish

Kirish: Ushbu qo'llanmada siz PostgreSQL EXISTS operatori yordamida quyi so'rov birorta qator qaytargan yoki qaytarmaganligini tekshirishni o'rganasiz.

PostgreSQL EXISTS operatoriga kirish

PostgreSQLda EXISTS operatori quyi so'rov kamida bitta qator qaytarganligini tekshiradi. Agar quyi so'rov birorta qator topsa, EXISTS operatori true (rost) qaytaradi, aks holda false (yolg'on) qaytaradi.

EXISTS operatorining sintaksisi:

query.sql

EXISTS (subquery)

Natijani inkor qilish (teskarisiga o'girish) uchun NOT operatoridan foydalaniladi:

query.sql

NOT EXISTS (subquery)

Odatda EXISTS operatori SELECT, UPDATE va DELETE buyruqlarining WHERE shartida qo'llaniladi.

SELECT buyrug'ida EXISTS operatori

Quyidagi so'rov kamida bitta omborda saqlanayotgan (mavjud bo'lgan) barcha mahsulotlarni topadi:

query.sql

SELECT
  product_name
FROM
  products p
WHERE
  EXISTS (
    SELECT
      1
    FROM
      inventories i
    WHERE
      i.product_id = p.product_id
  );

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"

Ushbu misolda ichki so'rovda SELECT 1 ishlatilgan. EXISTS faqat qator bor-yo'qligini tekshirgani sababli, ustun qiymatlari ahamiyat kasb etmaydi. Shu sababli SELECT 1 yozish an'anaga aylangan bo'lib, u keraksiz ustunlarni yuklashdan qochadi va so'rovni tezlashtiradi.

UPDATE buyrug'ida EXISTS operatori

Quyidagi buyruq 1-raqamli omborda mavjud bo'lgan barcha tovarlar narxini 5% ga oshiradi:

query.sql

UPDATE products p
SET
  price = price * 1.05
WHERE
  EXISTS (
    SELECT
      1
    FROM
      inventories i
    WHERE
      i.product_id = p.product_id
      AND i.warehouse_id = 1
  ) 
RETURNING *;

Natija:

matn

product_id |        product_name        |  price  | brand_id | category_id
------------+----------------------------+---------+----------+-------------
          1 | Samsung Galaxy S24         | 1049.99 |        1 |           1
          4 | Xiaomi Mi 14               |  839.99 |        4 |           2
          7 | Apple iPhone 15 Pro Max    | 1364.99 |        2 |           3
         10 | Apple AirPods Pro 3        |  262.49 |        2 |           5
         13 | Samsung Galaxy Watch 6     |  367.49 |        1 |           6
         16 | Samsung QN900C Neo QLED    | 3149.99 |        1 |           8
         19 | Bose SoundLink Max         |  419.99 |        7 |           9
         22 | Microsoft Surface Laptop 5 | 1364.99 |        9 |          11
         25 | Dell Inspiron 27           | 1049.99 |        7 |          12

DELETE buyrug'ida NOT EXISTS operatori

Hech qaysi omborda mavjud bo'lmagan (qoldig'i yo'q) tovarlarni o'chirib tashlash:

query.sql

DELETE FROM products p
WHERE
  NOT EXISTS (
    SELECT
      1
    FROM
      inventories i
    WHERE
      i.product_id = p.product_id
  ) 
RETURNING *;

Bunda ichki so'rov birorta ham qator topa olmasa, NOT EXISTS rost bo'ladi va ushbu mahsulot o'chirib tashlanadi.

Xulosa

Quyi so'rov birorta qator qaytarishini tekshirish uchun PostgreSQL EXISTS operatoridan foydalaning.
Birorta ham qator qaytmasligini tekshirish uchun NOT EXISTS operatorini ishlating.
EXISTS operatori ichida SELECT 1 dan foydalanish eng optimal usul hisoblanadi.

Oldingi

Keyingi