UNION

Kirish: ushbu qo'llanmada siz ikki so'rov natijalar to'plamini bitta natijalar to'plamiga birlashtirish uchun PostgreSQL UNION operatoridan qanday foydalanishni o'rganasiz.

PostgreSQL UNION operatori bilan tanishish

UNION operatori ikki yoki undan ortiq so'rovlar natijalar to'plamini bitta natijalar to'plamiga birlashtirish imkonini beradi. Quyida UNION operatorining sintaksisi keltirilgan:

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

Ushbu sintaksisda:

• Birinchidan, natijalar to'plamini birlashtirmoqchi bo'lgan ikkita SELECT so'rovini keltiring.
• Ikkinchidan, ikkita so'rov o'rtasida UNION kalit so'zidan foydalaning.

UNION operatori ikkinchi SELECT so'rovining natijalar to'plamini birinchi SELECT so'rovining natijalar to'plamiga qo'shadi. Bundan tashqari, u yakuniy natijalar to'plamidan takroriy qatorlarni (dublikatlarni) olib tashlaydi:

Ikkala SELECT so'rovi ham quyidagi qoidalarga mos kelishi kerak:

• Bir xil sondagi ustunlarni qaytarishi kerak.
• Mos keluvchi ustunlarning ma'lumotlar turlari bir-biriga mos kelishi kerak.

Namunaviy jadvallarni sozlash

Faraz qilaylik, bizda ikkita jadval mavjud:

• slow_movings jadvali kam sotiladigan (omborida uzoq turib qoladigan) mahsulotlarni saqlaydi.
• flagships jadvali flagman (yetakchi) 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 UNION operatori misoli

Aytaylik, siz barcha flagman va kam sotiladigan mahsulotlar ro'yxatini olmoqchisiz.

slow_movings va flagships jadvallaridan ma'lumotlarni olish uchun ikkita SELECT so'rovidan foydalanishingiz mumkin. So'ngra, ikkinchi so'rov natijalar to'plamini birinchi so'rov natijalar to'plamiga qo'shish uchun UNION operatoridan foydalanasiz:

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

Natija:

       name       |  brand
------------------+---------
 iPhone 16        | Apple
 iPhone 16 Plus   | Apple
 Galaxy Z Flip 6  | Samsung
 Galaxy S24 Ultra | Samsung
 iPhone 16 Pro    | Apple
 Galaxy S24       | Samsung
 Pixel 9          | Google

PostgreSQL UNION ni ORDER BY bandi bilan ishlatish

UNION operatori qaytargan natijalar to'plamini saralash uchun siz ORDER BY bandidan foydalanishingiz mumkin. Biroq, siz ORDER BY bandini ikkinchi so'rovning oxiriga qo'yishingiz kerak:

SELECT column1, column2 
FROM tableA
UNION
SELECT column1, column2 
FROM tableB
ORDER BY column1, column2;

Masalan, quyidagi so'rov natijalar to'plamlarini birlashtirish uchun UNION operatoridan foydalanadi va ro'yxatni nomlar bo'yicha saralash uchun ORDER BY bandini qo'llaydi:

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

Natija:

       name       |  brand
------------------+---------
 Galaxy S24       | Samsung
 Galaxy S24 Ultra | Samsung
 Galaxy Z Flip 6  | Samsung
 iPhone 16        | Apple
 iPhone 16 Plus   | Apple
 iPhone 16 Pro    | Apple
 Pixel 9          | Google

Natijalar to'plamiga ustunlar qo'shish

Ba'zan siz qator qaysi natijalar to'plamidan kelganligini belgilash uchun qo'shimcha ustun qo'shishni xohlashingiz mumkin. Buning uchun siz o'zgarmas qiymat va ustun taxallusini quyidagicha ko'rsatasiz:

SELECT
  column1,
  column2,
  value AS column3
FROM
  table1
UNION
SELECT
  column1,
  column2,
  value AS column3
FROM
  table2
ORDER BY
  sort_expression;

Masalan, siz natijalar to'plamiga inventory_type ustunini quyidagicha qo'shishingiz mumkin:

SELECT
  name,
  brand,
  'Slow moving' inventory_type
FROM
  slow_movings
UNION
SELECT
  name,
  brand,
  'Flagship' inventory_type
FROM
  flagships
ORDER BY name;

Natija:

       name       |  brand  | inventory_type
------------------+---------+----------------
 Galaxy S24       | Samsung | Flagship
 Galaxy S24       | Samsung | Slow moving
 Galaxy S24 Ultra | Samsung | Flagship
 Galaxy Z Flip 6  | Samsung | Slow moving
 iPhone 16        | Apple   | Flagship
 iPhone 16        | Apple   | Slow moving
 iPhone 16 Plus   | Apple   | Slow moving
 iPhone 16 Pro    | Apple   | Flagship
 Pixel 9          | Google  | Flagship
 Pixel 9          | Google  | Slow moving

UNION ALL operatori bilan takroriy qatorlarni kiritish

Quyidagi so'rov flagman va kam sotiladigan mahsulotlarning brend nomlarini birlashtirish uchun UNION operatoridan foydalanadi:

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

Natija:

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

Yakuniy natijalar to'plamiga faqat uchta alohida (unikal) brend nomi kiritildi.

Buning sababi shundaki, UNION operatori takrorlanuvchi brend nomlarini olib tashlaydi. Takroriy qatorlarni ham saqlab qolish uchun UNION ALL operatoridan foydalanishingiz mumkin.

PostgreSQL UNION ALL operatori

UNION ALL operatori ikkinchi so'rov natijalar to'plamini birinchi so'rov natijalar to'plamiga qo'shadi va yakuniy natijalar to'plamida takroriy qatorlarni saqlab qoladi:

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

Masalan, quyidagi so'rov barcha brend nomlarini takrorlari bilan birga olish uchun UNION ALL operatoridan foydalanadi:

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

Natija:

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

Xulosa

• So'rov natijalar to'plamini boshqa so'rov natijalar to'plamiga qo'shish va yakuniy natijalar to'plamidan takroriy qatorlarni olib tashlash uchun UNION operatoridan foydalaning.
• Takroriy qatorlarni ham saqlab qolish uchun UNION ALL operatoridan foydalaning.
• Yakuniy natijalar to'plamidagi qatorlarni saralash uchun ORDER BY bandini ikkinchi so'rov oxiriga joylashtiring.