Küme işlemleri ve gelişmiş birleştirmeler

Okul aynı bülteni hem müşterilere hem öğrencilere göndermek istiyor; iki tablodan tek bir adres listesi gerekiyor. Müdür soruyor: “Hangi şehirlerde hem öğrencimiz hem de müşterimiz var?”, satış ekibi ise “hiç elektronik almamış müşterileri” arıyor. Bu sorular en kolay matematikteki kümeler diliyle cevaplanır: birleşim, kesişim, fark. SQL'de bunlar için özel işleçler vardır; birkaç “sıra dışı” birleştirme türü de tabloyu tamamlar.

UNION ve UNION ALL

UNION iki sorgunun sonucunu alt alta yazar ve tekrarları siler, UNION ALL ise tekrarları tutar. Kurallar: iki sorguda da sütun sayısı aynı, tipleri uyumlu olmalıdır; sütun adları ilk sorgudan alınır; ORDER BY yalnızca bir kez, en sonda yazılır ve tüm sonuca uygulanır.

SELECT
  (SELECT COUNT(*) FROM (SELECT city FROM students
                         UNION
                         SELECT city FROM customers)) AS with_union,
  (SELECT COUNT(*) FROM (SELECT city FROM students
                         UNION ALL
                         SELECT city FROM customers)) AS with_union_all;

with_union | with_union_all
11 | 19

SELECT 'student' AS type, first_name AS name, city
FROM students
WHERE city = 'Bakı'
UNION ALL
SELECT 'customer', name, city
FROM customers
WHERE city = 'Bakı'
ORDER BY type, name;

type | name | city
customer | Anar Mustafayev | Bakı
customer | Zəhra Hüseynli | Bakı
student | Aysel | Bakı
student | Kamran | Bakı
student | Leyla | Bakı
student | Rəşad | Bakı

INTERSECT ve EXCEPT

INTERSECT iki sonuçta da bulunan satırları (kesişim), EXCEPT ise birinci sonuçta olup ikincide olmayan satırları (fark) döndürür. İkisi de tekrarları siler. Aşağıda hem öğrencimizin hem müşterimizin olduğu şehirleri, sonra da yalnızca öğrencilerin yaşadığı şehirleri buluyoruz.

SELECT city FROM students
INTERSECT
SELECT city FROM customers
ORDER BY city;

city
Bakı
Gəncə

SELECT city FROM students
EXCEPT
SELECT city FROM customers
ORDER BY city;

city
Lənkəran
Naxçıvan
Quba
Sumqayıt
Şəki

Kendine birleştirme ve CROSS JOIN

Kendine birleştirmede aynı tablo iki farklı takma adla iki kez kullanılır; sanki iki kopyası varmış gibi. Örneğin aynı şehirden öğrencileri ikişer ikişer tanıştırmak istiyoruz. a.city = b.city koşulu şehirleri eşleştirir, a.id < b.id ise her çifti yalnızca bir kez tutar.

SELECT a.first_name AS student_1,
       b.first_name AS student_2,
       a.city
FROM students AS a
JOIN students AS b ON b.city = a.city AND a.id < b.id
ORDER BY a.city, a.id, b.id;

student_1 | student_2 | city
Aysel | Leyla | Bakı
Aysel | Rəşad | Bakı
Aysel | Kamran | Bakı
Leyla | Rəşad | Bakı
Leyla | Kamran | Bakı
Rəşad | Kamran | Bakı
Murad | Tural | Gəncə
Elvin | Orxan | Sumqayıt

CROSS JOIN tüm kombinasyonları bilerek kurar. Genellikle bir hatadır, ama bazen tam da gereken şeydir: “her öğrenci × her ders” tablosunu kurup ona gerçek kayıtları LEFT JOIN ile bağlarsak kimin hangi derse kayıtlı olmadığını görürüz. Aşağıda 8. sınıf öğrencileri ve matematik dersleri için böyle bir matris var: NULL, kayıt olmadığı anlamına gelir.

SELECT s.first_name, c.title, e.score
FROM students AS s
CROSS JOIN courses AS c
LEFT JOIN enrollments AS e
       ON e.student_id = s.id AND e.course_id = c.id
WHERE s.grade = 8 AND c.subject = 'Math'
ORDER BY s.id, c.id;

first_name | title | score
Leyla | Algebra | NULL
Leyla | Geometry | 95
Günay | Algebra | NULL
Günay | Geometry | NULL
Orxan | Algebra | NULL
Orxan | Geometry | 70

Anti-birleştirme: NOT EXISTS

“Hiç elektronik almamış müşteriler” bir anti-birleştirme sorusudur: soldaki tablonun, sağdaki tabloda eşi olmayan satırlarını tutarız. En güvenilir yazım NOT EXISTS ifadesidir: alt sorgu her müşteri için “elektronik siparişi var mı?” diye sorar; cevap “yok” ise müşteri sonuca girer.

SELECT c.name, c.country
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders AS o
  JOIN products AS p ON p.id = o.product_id
  WHERE o.customer_id = c.id
    AND p.category = 'Electronics'
)
ORDER BY c.name;

name | country
John Carter | United Kingdom
Mehmet Kaya | Türkiye
Olga Ivanova | Russia

Tersi olan soru, yani “en az bir kez elektronik alanlar”, EXISTS ile yazılır (yarı birleştirme). Bunu sıradan bir JOIN ile yazarsan iki elektronik siparişi olan Anar sonuçta iki kez görünür; EXISTS ise her müşteriyi bir kez döndürür, çünkü eşi bulur bulmaz aramayı bırakır.

SELECT c.title
FROM courses AS c
JOIN enrollments AS e ON e.course_id = c.id
JOIN students AS s ON s.id = e.student_id
WHERE s.city = 'Bakı'
-- INTERSECT the same query for 'Sumqayıt'
ORDER BY title;

title
Geometry
Mechanics

SELECT s.first_name
FROM students AS s
WHERE NOT EXISTS (
  -- an enrollment of this student in a Math course
)
ORDER BY s.first_name;

first_name
Elvin
Günay
Kamran
Rəşad
Tural