7-dars. Set operatorlari

Set operatorlari

Set Operators — bir nechta SELECT natijalarini birlashtirish yoki solishtirish uchun ishlatiladi.

Biz bu darsda:

• UNION

• UNION ALL

• EXCEPT

• INTERSECT operatorlarini o‘rganamiz.

Muhim qoida!

Set Operator ishlaganda:

✅ ustunlar soni bir xil bo‘lishi kerak

✅ datatype’lar mos bo‘lishi kerak

Misol uchun jadvallar

UNION

UNION — ikkita SELECT natijasini birlashtiradi va duplicate (takrorlanuvchi qiymat) larni olib tashlaydi.

SELECT name FROM Students_A
UNION
SELECT name FROM Students_B

name

Ali

Vali

Sardor

Jasur

UNION ALL

UNION ALL — natijalarni birlashtiradi, lekin duplicate’larni olib tashlamaydi.

SELECT name FROM Students_A
UNION ALL
SELECT name FROM Students_B

name

Ali

Vali

Sardor

Vali

Sardor

Jasur

UNION vs UNION ALL

EXCEPT

EXCEPT — birinchi jadvalda bor, ikkinchisida yo‘q ma’lumotlarni chiqaradi.

SELECT name FROM Students_A
EXCEPT
SELECT name FROM Students_B

name

Ali

INTERSECT

INTERSECT — ikkala SELECT’da ham mavjud bo‘lgan ma’lumotlarni chiqaradi.

SELECT name FROM Students_A
INTERSECT
SELECT name FROM Students_B

name

Vali

Sardor

Oddiy eslab qolish usuli

Set operatorlari uchun asosiy qoidalar

Set operatorlari (UNION, UNION ALL, INTERSECT, EXCEPT) ikki yoki undan ortiq SELECT natijalarini birlashtirish uchun ishlatiladi.

#1 ORDER BY faqat bir marta ishlatiladi

ORDER BY oxirida yoziladi. Har bir SELECT ichida alohida yozilmaydi.

❌ Noto‘g‘ri:

SELECT name FROM students
ORDER BY name
UNION
SELECT name FROM teachers
ORDER BY name;

✅ To‘g‘ri:

SELECT name FROM students
UNION
SELECT name FROM teachers
ORDER BY name;

Oddiy tushuncha:

Barcha natijalar birlashgandan keyin tartiblanadi.

#2 Ustunlar soni bir xil bo‘lishi kerak

Ikkala SELECT ham bir xil miqdordagi ustun qaytarishi kerak.

❌ Noto‘g‘ri:

SELECT id, name FROM students
UNION
SELECT name FROM teachers;

Birinchi query → 2 ta ustun

Ikkinchi query → 1 ta ustun

✅ To‘g‘ri:

SELECT id, name FROM students
UNION
SELECT id, name FROM teachers;

SQL ustunlarni bir-biriga moslab qo‘yadi. Soni teng bo‘lishi kerak.

#3 Data typelar mos bo‘lishi kerak

Mos ustunlarning turlari o‘xshash bo‘lishi kerak.

❌ Muammo bo‘lishi mumkin:

SELECT name FROM students
UNION
SELECT salary FROM employees;

name → text

salary → number

✅ To‘g‘ri:

SELECT name FROM students
UNION
SELECT department_name FROM departments;

Text bilan text, son bilan son birlashtiriladi.

#4 Ustunlar tartibi bir xil bo‘lishi kerak

SQL ustun nomiga emas, joylashuviga qaraydi.

❌ Xavfli:

SELECT id, name FROM students
UNION
SELECT name, id FROM teachers;

Bu yerda:

• id joyiga name

• name joyiga id

tushib ketadi.

✅ To‘g‘ri:

SELECT id, name FROM students
UNION
SELECT id, name FROM teachers;

1-ustun 1-ustun bilan, 2-ustun 2-ustun bilan mos keladi.

#5 Ustun nomini birinchi query belgilaydi

Natijadagi ustun nomlari birinchi SELECT dan olinadi.

SELECT name AS full_name FROM students
UNION
SELECT teacher_name FROM teachers;

Natijadagi ustun nomi:

full_name

bo‘ladi.

SQL birinchi query’ni “asosiy” deb oladi.

#6 To‘g‘ri ustunlarni moslashtirish kerak

Bir xil ma’nodagi ustunlarni birlashtirish kerak.

❌ Mantiqan noto‘g‘ri:

SELECT name, age FROM students
UNION
SELECT product_name, price FROM products;

Bu ma’lumotlar bir-biriga bog‘liq emas.

✅ To‘g‘ri:

SELECT name, city FROM customers
UNION
SELECT name, city FROM suppliers;

O‘xshash ma’lumotlarni birlashtiring.

SET operatorlari vs JOINS

Darsga tegishli SQL fayllar

⬇️ Darsda ishlatilgan .sql fayli: 📎 dars_7.sql (1013 B)

⬇️ Uyga vazifa .sql fayli: 📎 dars_7_uy_ishi.sql (3.6 KB)