UNION, INTERSECT, EXCEPT

9-bob 10 kod bloki 2202 so'z

JOIN column qo'shadi — natijani kengaytiradi. To'plam amali esa row qo'shadi — natijani uzaytiradi. Shu bitta jumla butun bobning mazmuni; qolgani tafsilot.

UNION va UNION ALL

Ikkalasi ham ikkinchi natijani birinchisining tagiga qo'yadi. Farqi — takrorlar.

-- UNION ALL: hammasini saqlaydi, takrorlar bilan birga
SELECT city FROM students
UNION ALL
SELECT city FROM students;
-- 20 ta row

-- UNION: takroriy ROW larni olib tashlaydi
SELECT city FROM students
UNION
SELECT city FROM students;
-- 7 ta row — 6 ta har xil shahar va NULL

1-natija

… 600 qatordan birinchi 15 tasi

600 qator

2-natija

9 qator

1 ms

Realroq foydasi — maktabdagi hamma odamning yagona ro'yxati:

SELECT first_name || ' ' || last_name AS name, 'student' AS role, city AS extra
FROM students
UNION ALL
SELECT full_name, 'teacher', subject
FROM teachers
ORDER BY role, name;

… 312 qatordan birinchi 15 tasi

312 qator

1 ms

nameroleextra

Aziza KarimovastudentTashkent Bekzod ToshevstudentSamarkand ……… Kamola ErgashevateacherStatistics Nodira AlimovateacherData Science Rustam HalilovteacherDatabases Sardor YoqubovteacherProgramming

Qo'shilgan literal column — 'student' / 'teacher' ga e'tibor bering. Usiz keyinchalik ikkala guruhni ajratib bo'lmaydi — har xil narsalarni birga qo'yganda manbani doim belgilab qo'ying.

Qoidalar

1. Har bir shoxda column soni bir xil — aks holda xato.
2. Type lar o'rin bo'yicha mos kelishi kerak. Birinchi query ning 1-column i ikkinchisining 1-column i bilan qatorlashadi, nomlaridan qat'i nazar.
3. Column nomlari birinchi query dan olinadi. Keyingi shoxlardagi alias lar e'tiborga olinmaydi.
4. Bitta ORDER BY, eng oxirida, u birlashgan natijaga tegishli.

-- XATO: 2 ta column va 3 ta column
SELECT first_name, city FROM students
UNION ALL
SELECT full_name, subject, salary FROM teachers;

-- Tuzatilgan: shakllar mos kelishi uchun qisqa shoxni literal bilan to'ldiring
SELECT first_name, city,    NULL   AS salary FROM students
UNION ALL
SELECT full_name,  subject, salary          FROM teachers;

-- ORDER BY butun natijaga tegishli, shuning uchun oxirida va bir marta
SELECT first_name AS name FROM students
UNION ALL
SELECT full_name         FROM teachers
ORDER BY name;

:::gotcha ⚠️ O'rin bo'yicha, nom bo'yicha emas SELECT first_name, city ustiga SELECT subject, full_name yozishingizga hech narsa to'sqinlik qilmaydi — type lar mos keladi, demak u ishlaydi va city column iga ismlar aralashib chiqadi. SQL faqat o'rin bo'yicha moslaydi. Har bir shoxda column larni bir xil tartibda aniq sanab yozing va to'plam amalida hech qachon SELECT * ishlatmang. :::

INTERSECT va EXCEPT

INTERSECT ikkala natijada ham bor row larni qaytaradi. EXCEPT (Oracle da MINUS) birinchisida bor, ikkinchisida yo'q row larni qaytaradi. Ikkalasi ham takrorlarni olib tashlaydi.

-- Python Basics (1) NI HAM, SQL for Analytics (2) NI HAM oladigan student lar
SELECT student_id FROM enrollments WHERE course_id = 1
INTERSECT
SELECT student_id FROM enrollments WHERE course_id = 2;
-- 1 (Aziza), 4 (Jasur)

-- Python Basics ni oladigan, lekin SQL for Analytics ni OLMAYDIGAN student lar
SELECT student_id FROM enrollments WHERE course_id = 1
EXCEPT
SELECT student_id FROM enrollments WHERE course_id = 2;
-- 2 (Bekzod), 7 (Sevara), 10 (Ulugbek)

1-natija

… 60 qatordan birinchi 15 tasi

60 qator

2-natija

… 74 qatordan birinchi 15 tasi

74 qator

1 ms

Qo'lda tekshiring — sample data aynan shuning uchun kichik:

INTERSECT shuningdek "bularning hammasi bor" ni ifodalashning eng toza yo'li, aks holda bu noqulay bo'ladi:

-- 1 VA 2 VA 5-course larga yozilgan student lar
SELECT student_id FROM enrollments WHERE course_id = 1
INTERSECT
SELECT student_id FROM enrollments WHERE course_id = 2
INTERSECT
SELECT student_id FROM enrollments WHERE course_id = 5;
-- 1 (Aziza)

… 29 qatordan birinchi 15 tasi

29 qator

0 ms

:::gotcha ⚠️ Mavjudligi SQLite va PostgreSQL INTERSECT va EXCEPT ni qo'llab-quvvatlaydi. MySQL ularni faqat 8.0.31 da qo'shdi; undan oldin ularni INNER JOIN (intersect) va LEFT JOIN ... IS NULL (except) bilan yasash kerak edi. Oracle EXCEPT ni MINUS deb ataydi. :::

To'plam amallari va JOIN

NULL qatori — eng nozigi, va aynan shu sabab EXCEPT NOT IN yiqiladigan joyda muvaffaqiyat qozonadi.

Amaliy qo'llash

1. Table lar yoki davrlar bo'yicha ajratilgan data ni birlashtirish

Arxivlangan data, oylik partition lar, birlashtirilayotgan ikkita tizim — hammasi UNION ALL:

SELECT * FROM enrollments_2025
UNION ALL
SELECT * FROM enrollments_2026;

1. Hisobotga jami qatorini qo'shish

SELECT c.title, COUNT(*) AS enrollments
FROM enrollments e JOIN courses c USING (course_id)
GROUP BY c.course_id, c.title

UNION ALL

SELECT 'JAMI', COUNT(*) FROM enrollments

ORDER BY (title = 'JAMI'), enrollments DESC;

JAMI20

ORDER BY (title = 'JAMI') usuli FALSE (0) ni TRUE (1) dan oldin saralaydi va jami qatorini pastga mahkamlaydi.

3. Unpivot — column larni row larga aylantirish

CASE row larni column ga pivot qiladi (8-bob); UNION ALL teskarisini qiladi. Bu shakl aynan Seaborn xohlagan narsa — long format — va u qo'llanmaning II qismiga ko'prik:

SELECT title, 'price'   AS metric, price   AS value FROM courses
UNION ALL
SELECT title, 'credits',           credits          FROM courses
ORDER BY title, metric;

… 30 qatordan birinchi 15 tasi

30 qator

0 ms

FULL OUTER JOIN qo'llab-quvvatlanmagan joyda (eski SQLite, MySQL) uni ikkita LEFT JOIN dan quring:

SELECT c.title, e.enrollment_id
FROM courses c LEFT JOIN enrollments e ON c.course_id = e.course_id

UNION

SELECT c.title, e.enrollment_id
FROM enrollments e LEFT JOIN courses c ON c.course_id = e.course_id;

… 2501 qatordan birinchi 15 tasi

2501 qator

3 ms

Bu yerda UNION ALL emas, oddiy UNION to'g'ri — mos kelgan row lar ikkala yarmida ham chiqadi va ularni deduplikatsiya qilish kerak.

Mashqlar to'plami

-- 1. Student va teacher larning yagona ro'yxati
SELECT first_name || ' ' || last_name AS name, 'student' AS role FROM students
UNION ALL
SELECT full_name, 'teacher' FROM teachers
ORDER BY role, name;

-- 2. 1 va 2-course ni ham oladigan student lar
SELECT student_id FROM enrollments WHERE course_id = 1
INTERSECT
SELECT student_id FROM enrollments WHERE course_id = 2;

-- 3. Hech kim yozilmagan course lar
SELECT course_id FROM courses
EXCEPT
SELECT course_id FROM enrollments;

-- 4. Barcha har xil shahar va subject lar bitta column da
SELECT city AS value, 'city' AS kind FROM students WHERE city IS NOT NULL
UNION
SELECT subject, 'subject' FROM teachers
ORDER BY kind, value;

-- 5. Course ro'yxati va JAMI qatori
SELECT c.title, COUNT(*) AS n FROM enrollments e JOIN courses c USING (course_id)
GROUP BY c.course_id, c.title
UNION ALL
SELECT 'JAMI', COUNT(*) FROM enrollments
ORDER BY (title = 'JAMI'), n DESC;

-- 6. Course metrikalarini long format ga unpivot qilish (Seaborn uchun tayyor)
SELECT title, 'price' AS metric, price AS value FROM courses
UNION ALL
SELECT title, 'credits', credits FROM courses;

-- 7. Active enrollment i bor, lekin hech qachon completed bo'lmagan student lar
SELECT student_id FROM enrollments WHERE status = 'active'
EXCEPT
SELECT student_id FROM enrollments WHERE status = 'completed';

-- 8. UNION va UNION ALL farqini o'zingizga isbotlang
SELECT COUNT(*) FROM (SELECT city FROM students UNION ALL SELECT city FROM students);  -- 20
SELECT COUNT(*) FROM (SELECT city FROM students UNION     SELECT city FROM students);  -- 7

Intervyu savollari

S: UNION va UNION ALL?

UNION takroriy row larni olib tashlaydi va shuning uchun butun natijani saralashi yoki hash qilishi kerak; UNION ALL hammasini saqlaydi va ancha tez. Deduplikatsiya haqiqatan kerak bo'lmasa, UNION ALL ni afzal ko'ring.

S: UNION va JOIN?

UNION row larni vertikal qo'yadi va column soni hamda type lari mos kelishini talab qiladi. JOIN column larni gorizontal birlashtiradi va mos kelish shartini talab qiladi. Kengroq va uzunroq.

S: UNION uchun qanday talablar bor?

Har bir shoxda bir xil column soni, o'rin bo'yicha moslashtirilgan mos type lar va oxirida bitta ORDER BY. Column nomlari birinchi shoxdan olinadi.

S: INTERSECT va EXCEPT nima qiladi?

INTERSECT ikkala natijada ham bor row larni qaytaradi; EXCEPT birinchisida bor, ikkinchisida yo'q row larni qaytaradi. Ikkalasi ham deduplikatsiya qiladi. Oracle EXCEPT ni MINUS deydi; MySQL ularni faqat 8.0.31 da oldi.

S: Nega EXCEPT NOT IN dan xavfsizroq bo'lishi mumkin?

To'plam amallari NULL larni teng deb biladi, shuning uchun ikkinchi natijadagi NULL javobni NOT IN dagidek yo'q qilib yubormaydi.

S: Guruhlangan hisobotga jami qatorini qanday qo'shasiz?

Umumiy jamini literal yorliq bilan hisoblaydigan ikkinchi query ni UNION ALL qiling, keyin yorliq oxirida turadigan qilib saralang. (Ba'zi database larda GROUP BY ROLLUP ham bor.)

S: FULL OUTER JOIN ni qanday yasaysiz?

A LEFT JOIN B UNION B LEFT JOIN A — aynan oddiy UNION bilan, shunda ikkala yarmida mos kelgan row lar deduplikatsiya qilinadi.

Xulosa

• JOIN natijani kengaytiradi (column), to'plam amallari uzaytiradi (row).
• UNION ALL takrorlarni saqlaydi va tez — uni standart qiling. UNION deduplikatsiya qiladi va saralashga pul to'laydi.
• Qoidalar: bir xil column soni, nom bo'yicha emas, o'rin bo'yicha mos type lar, nomlar birinchi shoxdan, oxirida bitta ORDER BY.
• Shakllar mos kelishi uchun qisqa shoxni NULL AS col bilan to'ldiring va manbalarni ajratish uchun har bir shoxni literal bilan belgilang.
• INTERSECT = ikkalasida ham. EXCEPT = faqat birinchisida. Ikkalasi ham deduplikatsiya qiladi va NULL larni teng deb biladi, bu esa EXCEPT ni NULL ga xavfsiz anti-join qiladi.
• Amaliy foydasi: davrlar yoki arxivlarni birga qo'yish, JAMI qatorini qo'shish, chizish uchun long format ga unpivot qilish va FULL OUTER JOIN ni yasash.

Keyingi: 10-bob — data yaratish va o'zgartirish: CREATE, INSERT, UPDATE, DELETE, constraint lar, transaction lar va normalization.