EXCEPT

Kirish: Ushbu qo'llanmada siz PostgreSQL EXCEPT operatori yordamida ikkita so'rov natijalarini solishtirish va birinchi so'rovda bor, lekin ikkinchi so'rovda yo'q bo'lgan yozuvlarni ajratib olishni o'rganasiz.

PostgreSQL EXCEPT operatoriga kirish

EXCEPT operatori ikkita so'rov natijasini solishtirib, ular o'rtasidagi ayirmani (farqni) topish imkonini beradi.

EXCEPT operatori birinchi natijalar to'plamida mavjud bo'lgan, ammo ikkinchi natijalar to'plamida uchramaydigan qatorlarni qaytaradi.

EXCEPT operatorining sintaksisi quyidagicha:

SELECT column1, column2
FROM tableA
EXCEPT
SELECT column1, column2
FROM tableB;

Ushbu sintaksisda:

• Birinchidan, natijalarini birlashtirmoqchi / solishtirmoqchi bo'lgan ikkita SELECT buyrug'ini yozasiz.
• Ikkinchidan, ikkala SELECT buyrug'i o'rtasida EXCEPT operatorini joylashtirasiz.

EXCEPT operatori to'g'ri ishlashi uchun SELECT so'rovlari quyidagi qoidalarga mos bo'lishi kerak:

• Ular bir xil sondagi ustunlarni qaytarishi shart.
• Mos ustunlarning ma'lumot turlari bir-biriga mos (mos keluvchi) bo'lishi zarur.

EXCEPT operatori yakuniy natijalar to'plamidagi takrorlanuvchi (dublikat) qatorlarni avtomatik ravishda olib tashlaydi.

Agar yakuniy natijada takroriy qatorlar ham saqlanib qolishini istasangiz, EXCEPT ALL operatoridan foydalanasiz:

SELECT column1, column2
FROM tableA
EXCEPT ALL
SELECT column1, column2
FROM tableB;

Amaliyotda EXCEPT operatori ma'lumotlarni o'zaro taqqoslash va chuqur tahlil qilish uchun juda qo'l keladi.

Namunaviy jadvallarni yaratish

Aytaylik, bizda ikkita jadval mavjud:

• slow_movings jadvali sust sotilayotgan tovarlarni saqlaydi.
• flagships jadvali flagman (yetakchi) tovarlarni saqlaydi.

Jadvallarni yaratish va ularga dastlabki ma'lumotlarni kiritish uchun SQL skripti:

CREATE TABLE slow_movings (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  brand VARCHAR(50) NOT NULL
);

CREATE TABLE flagships (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  brand VARCHAR(50) NOT NULL
);

INSERT INTO
  slow_movings (name, brand)
VALUES
  ('iPhone 16', 'Apple'),
  ('Galaxy S24', 'Samsung'),
  ('Pixel 9', 'Google'),
  ('iPhone 16 Plus', 'Apple'),
  ('Galaxy Z Flip 6', 'Samsung');

INSERT INTO
  flagships (name, brand)
VALUES
  ('iPhone 16', 'Apple'),
  ('Galaxy S24', 'Samsung'),
  ('Pixel 9', 'Google'),
  ('iPhone 16 Pro', 'Apple'),
  ('Galaxy S24 Ultra', 'Samsung');

PostgreSQL EXCEPT operatoriga misol

Quyidagi misolda EXCEPT operatori yordamida flagman bo'lgan, lekin sust sotilayotgan tovarlar qatoriga kirmaydigan mahsulotlar topiladi:

SELECT
  name,
  brand
FROM
  flagships
EXCEPT
SELECT
  name,
  brand
FROM
  slow_movings;

Natija:

       name        |  brand
-------------------+---------
 Galaxy S24 Ultra  | Samsung
 iPhone 16 Pro     | Apple

UNION va INTERSECT operatorlaridan farqli o'laroq, EXCEPT operatorida SELECT so'rovlarining ketma-ketlik tartibi juda muhim, chunki bu to'g'ridan-to'g'ri natijaga ta'sir qiladi.

Masalan, sust sotiladigan, lekin flagman bo'lmagan tovarlarni topish uchun so'rovlar tartibini almashtiramiz:

SELECT
  name,
  brand
FROM
  slow_movings
EXCEPT
SELECT
  name,
  brand
FROM
  flagships;

Natija:

      name       |  brand
-----------------+---------
 iPhone 16 Plus  | Apple
 Galaxy Z Flip 6 | Samsung

PostgreSQL EXCEPT operatorida ORDER BY bandi

Agar EXCEPT operatori qaytargan natijalar to'plamini tartiblashni istasangiz, ORDER BY bandini ikkinchi SELECT so'rovi oxiriga joylashtirishingiz mumkin:

SELECT
  column1,
  column2
FROM
  table1
EXCEPT
SELECT
  column1,
  column2
FROM
  table2
ORDER BY
  sort_expression;

Masalan, quyidagi so'rov sust sotilmaydigan flagman mahsulotlarni topadi va ularni brend bo'yicha saralaydi:

SELECT
  name,
  brand
FROM
  flagships
EXCEPT
SELECT
  name,
  brand
FROM
  slow_movings
ORDER BY
  brand;

Natija:

       name        |  brand
-------------------+---------
 iPhone 16 Pro     | Apple
 Galaxy S24 Ultra  | Samsung

Xulosa

• Ikki natijalar to'plami o'rtasidagi ayirmani topish uchun PostgreSQL EXCEPT operatoridan foydalaning.
• Natijada takroriy qatorlarni saqlab qolish uchun EXCEPT ALL operatoridan foydalaning.
• Natijalar to'plamidagi qatorlarni tartiblash uchun ORDER BY bandini ikkinchi SELECT so'roviga qo'shing.