UNION, INTERSECT, EXCEPT

Ba’zan bir nechta so‘rov natijasini bitta ro‘yxatga birlashtirish yoki ularni solishtirish kerak bo‘ladi. Buning uchun to‘plam amallari bor.

Shart: ustunlar mos bo‘lsin

Birlashtirilayotgan so‘rovlarda ustunlar soni bir xil va turlari mos bo‘lishi kerak. Natijadagi ustun nomlari birinchi so‘rovdan olinadi.

UNION va UNION ALL

Universitetdagi barcha odamlar — talabalar va ustozlar — bitta ro‘yxatda:

SELECT first_name, last_name, 'talaba' AS role FROM students
UNION ALL
SELECT first_name, last_name, 'ustoz' FROM teachers
ORDER BY last_name
LIMIT 10;

• UNION ALL — hammasini qo‘shadi, takrorlar ham qoladi (tezroq);
• UNION — takrorlarni olib tashlaydi.

SELECT city FROM students
UNION
SELECT 'Toshkent';

INTERSECT — ikkalasida ham bor

Talabalar orasida ham, ustozlar orasida ham uchraydigan familiyalar:

SELECT last_name FROM students
INTERSECT
SELECT last_name FROM teachers;

EXCEPT — birinchisida bor, ikkinchisida yo‘q

Talabalarda uchraydigan, lekin ustozlarda uchramaydigan familiyalar:

SELECT last_name FROM students
EXCEPT
SELECT last_name FROM teachers;

Esda qoldiring

• UNION ALL — qo‘shish, UNION — takrorsiz qo‘shish.
• INTERSECT — umumiy qismi, EXCEPT — farqi.
• So‘rovlarda ustunlar soni va turlari mos bo‘lsin.