UNION, UNION ALL, INTERSECT, EXCEPT

~15 daq · MySQL · Subquery va CTE

Muammodan boshlaylik

Yangi yil tabrigini yuborish kerak. Kontaktlar ikki jadvalda:

  • mijozlar — sayt orqali ro'yxatdan o'tganlar
  • obunachilar — faqat yangiliklarga obuna bo'lganlar (email va ism)

Ba'zi odamlar ikkalasida ham bor. Bitta umumiy, takrorlanmaydigan ro'yxat kerak.

JOIN jadvallarni yonma-yon birlashtiradi (ustunlar qo'shiladi). Bu yerda esa natijalarni ustma-ust qo'yish kerak (qatorlar qo'shiladi). Buning uchun — to'plam operatorlari.

UNION — birlashtirish

CREATE TABLE obunachilar (
    email VARCHAR(255) PRIMARY KEY,
    ism   VARCHAR(100)
);
INSERT INTO obunachilar VALUES
    ('aziz@mail.uz', 'Aziz'),          -- mijoz ham
    ('sevara@mail.uz', 'Sevara'),
    ('bobur@mail.uz', 'Bobur');

SELECT ism, email FROM mijozlar
UNION
SELECT ism, email FROM obunachilar;

+-----------------+----------------+
| ism             | email          |
+-----------------+----------------+
| Aziz Karimov    | aziz@mail.uz   |
| Malika Rahimova | malika@mail.uz |
| Jasur Toshev    | jasur@mail.uz  |
| Nodira Usmonova | nodira@mail.uz |
| Aziz            | aziz@mail.uz   |   ← ?!
| Sevara          | sevara@mail.uz |
| Bobur           | bobur@mail.uz  |
+-----------------+----------------+

UNION to'liq bir xil qatorlarni olib tashlaydi. ('Aziz Karimov', 'aziz@...') va ('Aziz', 'aziz@...') — har xil qatorlar (ism farq qiladi). Faqat email bo'yicha noyob ro'yxat kerak bo'lsa:

SELECT email FROM mijozlar
UNION
SELECT email FROM obunachilar;
-- 6 ta noyob email

Qoidalar

  1. Har ikki SELECT da ustunlar soni bir xil
  2. Mos ustunlar turlari mos bo'lishi kerak
  3. Natija ustun nomlari — birinchi SELECT dan
SELECT ism, email FROM mijozlar
UNION
SELECT email FROM obunachilar;
-- ERROR 1222: The used SELECT statements have a different number of columns

UNION ALL — takrorlar bilan

SELECT email FROM mijozlar
UNION ALL
SELECT email FROM obunachilar;
-- 7 qator: aziz@mail.uz ikki marta
UNION UNION ALL
Takrorlar Olib tashlanadi Qoladi
Tezlik Sekinroq (saralash/xeshlash kerak) Tez

Qoida: takror bo'lishi mumkin emasligini bilsangiz yoki takrorlar kerak bo'lsa — UNION ALL. Ko'pchilik UNION ni odat bo'yicha yozadi va keraksiz ish qildiradi.

Manbani belgilash

SELECT email, 'mijoz' AS manba FROM mijozlar
UNION ALL
SELECT email, 'obunachi' FROM obunachilar
ORDER BY email;

ORDER BY — butun natijaga, oxirida bir marta yoziladi.

Amaliy misol: umumiy faollik lentasi

SELECT 'buyurtma' AS tur, id, yaratilgan AS vaqt FROM buyurtmalar
UNION ALL
SELECT 'yangi mijoz', id, yaratilgan FROM mijozlar
UNION ALL
SELECT 'yangi mahsulot', id, yaratilgan FROM mahsulotlar
ORDER BY vaqt DESC
LIMIT 20;

Turli jadvallardagi hodisalar — bitta xronologik lentada. Admin panellardagi "So'nggi faollik" bloki.

INTERSECT — kesishma (MySQL 8.0.31+)

Ikkala natijada ham bor qatorlar:

-- Ham mijoz, ham obunachi
SELECT email FROM mijozlar
INTERSECT
SELECT email FROM obunachilar;
-- aziz@mail.uz

Eski versiyalarda — IN yoki JOIN bilan:

SELECT email FROM mijozlar
WHERE email IN (SELECT email FROM obunachilar);

EXCEPT — ayirma (MySQL 8.0.31+)

Birinchi natijada bor, ikkinchisida yo'q qatorlar:

-- Obunachi, lekin hali mijoz emas — ularga ro'yxatdan o'tish taklifi
SELECT email FROM obunachilar
EXCEPT
SELECT email FROM mijozlar;
-- sevara@mail.uz, bobur@mail.uz

Tartib muhim: A EXCEPT B ≠ B EXCEPT A.

Eski versiyalarda — NOT EXISTS (22-dars):

SELECT o.email FROM obunachilar o
WHERE NOT EXISTS (SELECT 1 FROM mijozlar m WHERE m.email = o.email);

Venn diagrammasi bilan

   mijozlar        obunachilar
  ┌─────────┬───┬─────────┐
  │ malika  │   │ sevara  │
  │ jasur   │aziz│ bobur  │
  │ nodira  │   │         │
  └─────────┴───┴─────────┘

UNION         → hammasi: malika, jasur, nodira, aziz, sevara, bobur
INTERSECT     → o'rtasi: aziz
mijoz EXCEPT  → faqat chap: malika, jasur, nodira
obuna EXCEPT  → faqat o'ng: sevara, bobur

Versiyani tekshirish

SELECT VERSION();

INTERSECT va EXCEPT faqat MySQL 8.0.31 va undan yuqorida. MariaDB da 10.3 dan beri bor. Eski versiyada IN / NOT EXISTS ishlating — natija bir xil.

Nima uchun shunday?

Nima uchun UNION standart holatda takrorlarni olib tashlaydi, UNION ALL esa alohida yozilishi kerak?

SQL to'plamlar nazariyasiga asoslangan, u yerda to'plamda takror elementlar bo'lmaydi: {1, 2} ∪ {2, 3} = {1, 2, 3}. Shuning uchun "sof" UNION — matematik birlashma. Amalda esa ko'pincha takrorlar muhim (masalan, barcha tranzaksiyalar) yoki ular bo'lmasligi aniq — shunda takror qidirish keraksiz ish. ALL so'zi — "to'plam emas, ro'yxat kerak" degan aniq signal.

Xulosa

Eslab qoling:

  • UNION — natijalarni ustma-ust qo'yish, takrorlarsiz
  • UNION ALL — takrorlar bilan, tezroq; kerak bo'lmasa takror qidirtirmang
  • Ustunlar soni va turlari mos; nomlar birinchi SELECT dan; ORDER BY oxirida
  • INTERSECT — ikkalasida ham bor; EXCEPT — birinchisida bor, ikkinchisida yo'q
  • INTERSECT/EXCEPT — MySQL 8.0.31+; eskisida IN / NOT EXISTS
  • JOIN — ustunlarni yonma-yon, UNION — qatorlarni ustma-ust

Keyingi dars

25-dars: String funksiyalar — CONCAT, LENGTH, SUBSTRING, REPLACE, TRIM