INTERSECT

Kirish: ushbu qo'llanmada siz ikki natijalar to'plamining umumiy yozuvlarini topish uchun PostgreSQL INTERSECT operatoridan qanday foydalanishni o'rganasiz.

PostgreSQL INTERSECT operatori bilan tanishish

INTERSECT operatori ikkita so'rov natijalar to'plamining umumiy yozuvlarini qaytaradi. Boshqacha qilib aytganda, u ikkita natijalar to'plamining kesishmasini qaytaradi.

Quyida INTERSECT operatorining sintaksisi keltirilgan:

SELECT column1, column2 
FROM table1
INTERSECT
SELECT column1, column2 
FROM table2;

Ushbu sintaksisda:

• Birinchidan, kesishma uchun ikkita natijalar to'plamini qaytaruvchi ikkita SELECT so'rovini keltiring.
• Ikkinchidan, ikkala natijalar to'plami o'rtasidagi kesishmani topish uchun ikkita SELECT so'rovi orasida INTERSECT operatoridan foydalaning.

Ikkala SELECT so'rovi ham quyidagi qoidalarga rioya qilishi kerak:

• Natijalar to'plamlarida ustunlar soni bir xil bo'lishi kerak.
• Mos ustunlar bir-biriga mos keluvchi ma'lumotlar turlariga ega bo'lishi kerak.

INTERSECT operatori ikkala so'rovdan faqat unikal (takrorlanmaydigan) umumiy qatorlarni kiritadi:

Natijaga takroriy qatorlarni ham kiritish uchun INTERSECT ALL operatoridan foydalanishingiz mumkin:

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

Namunaviy jadvallarni sozlash

Faraz qilaylik, bizda ikkita jadval mavjud: slow_movings va flagships.

slow_movings jadvali kam sotiladigan mahsulotlarni, flagships jadvali esa flagman mahsulotlarni saqlaydi:

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 INTERSECT operatori misoli

Quyidagi so'rov ham flagman, ham kam sotiladigan mahsulotlarni (ya'ni ikkala ro'yxatda ham mavjud bo'lgan mahsulotlarni) topish uchun INTERSECT operatoridan foydalanadi:

SELECT
  name
FROM
  flagships
INTERSECT
SELECT
  name
FROM
  slow_movings;

Natija:

    name
------------
 Pixel 9
 Galaxy S24
 iPhone 16

PostgreSQL INTERSECT operatori va ORDER BY bandi

INTERSECT operatori ishtirok etgan so'rov natijalar to'plamini saralash uchun ORDER BY bandini oxirgi so'rovga joylashtirasiz:

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

Masalan, quyidagi so'rov kam sotiladigan flagman mahsulotlarni topadi va ularni nomi bo'yicha saralaydi:

SELECT
  name
FROM
  flagships
INTERSECT
SELECT
  name
FROM
  slow_movings
ORDER BY name;

Natija:

    name
------------
 Galaxy S24
 iPhone 16
 Pixel 9

PostgreSQL INTERSECT ALL operatori misoli

Quyidagi so'rov kam sotiladigan flagman mahsulotlarning brendlarini topish uchun INTERSECT operatoridan foydalanadi:

SELECT
  brand
FROM
  flagships
INTERSECT
SELECT
  brand
FROM
  slow_movings
ORDER BY brand;

Natija:

  brand
---------
 Apple
 Google
 Samsung

U uchta unikal brendni qaytaradi.

Takroriy brendlarni ham o'z ichiga olgan holda barcha umumiy brendlarni topish uchun INTERSECT ALL operatoridan foydalanishingiz mumkin:

SELECT
  brand
FROM
  flagships
INTERSECT ALL
SELECT
  brand
FROM
  slow_movings
ORDER BY brand;

Natija:

  brand
---------
 Apple
 Apple
 Google
 Samsung
 Samsung

Xulosa

• Ikki natijalar to'plamining kesishmasini olish uchun PostgreSQL INTERSECT operatoridan foydalaning.
• Takroriy qatorlarni ham kiritish uchun INTERSECT ALL operatoridan foydalaning.
• INTERSECT operatori qaytargan yakuniy natijalar to'plamini saralash uchun ORDER BY bandini oxirgi so'rovga joylashtiring.