Küme İşlemleri

Önceki ders gruplamayı kurdu: tek bir sorgunun ürettiği satırlar kümelere ayrıldı ve her küme özetlendi. Birleştirme ile gruplama, hep tek bir sorgu içinde çalıştı.

Kimi soru ise iki ayrı sorgunun sonuçlarını karşılaştırmayı ister. “Bu iki listede ortak olanlar”, “birincide olup ikincide olmayanlar”, “iki listenin toplamı” — üçü de küme kuramının işlemleridir ve SQL’de doğrudan karşılıkları vardır. Bu ders o işlemleri kurar.

Ayrımı baştan koymak gerekir: birleştirme satırları yan yana ekler, sütun sayısını artırır. Küme işlemleri satırları alt alta ekler, sütun sayısını değiştirmez.

Aşağıdaki blok şemayı ve örnek veriyi kurar; dersin bütün sorguları bu blokta oluşturulan kutuphane.db dosyası üzerinde çalışır.

rm -f kutuphane.db
sqlite3 kutuphane.db <<'SQL'
CREATE TABLE sube (sube_id INTEGER PRIMARY KEY, ad TEXT NOT NULL, sehir TEXT NOT NULL);
CREATE TABLE kitap (kitap_id INTEGER PRIMARY KEY, baslik TEXT NOT NULL, yazar TEXT NOT NULL,
                    basim_yili INTEGER, sube_id INTEGER REFERENCES sube(sube_id));
CREATE TABLE uye (uye_id INTEGER PRIMARY KEY, ad TEXT NOT NULL, soyad TEXT NOT NULL,
                  eposta TEXT, kayit_tarihi TEXT NOT NULL);
CREATE TABLE odunc (odunc_id INTEGER PRIMARY KEY, kitap_id INTEGER NOT NULL REFERENCES kitap(kitap_id),
                    uye_id INTEGER NOT NULL REFERENCES uye(uye_id), alis_tarihi TEXT NOT NULL,
                    iade_tarihi TEXT);
INSERT INTO sube VALUES (1,'Merkez','Ankara'),(2,'Bahçelievler','Ankara'),
  (3,'Kadıköy','İstanbul'),(4,'Konak','İzmir');
INSERT INTO kitap VALUES (1,'Körlük','José Saramago',1995,1),(2,'Tutunamayanlar','Oğuz Atay',1972,1),
  (3,'Kum Kitabı','Jorge Luis Borges',1975,2),(4,'Yaban','Yakup Kadri',1932,2),
  (5,'Sessiz Ev','Orhan Pamuk',1983,3),(6,'Anayurt Oteli','Yusuf Atılgan',NULL,3),
  (7,'Tehlikeli Oyunlar','Oğuz Atay',1973,NULL);
INSERT INTO uye VALUES (1,'Ayşe','Demir','[email protected]','2023-02-14'),
  (2,'Mehmet','Kaya','[email protected]','2023-05-30'),(3,'Zeynep','Arslan',NULL,'2024-01-09'),
  (4,'Emre','Yıldız','[email protected]','2024-03-22'),(5,'Selin','Aydın',NULL,'2024-11-05'),
  (6,'Burak','Şahin','[email protected]','2025-01-18');
INSERT INTO odunc VALUES (1,1,1,'2025-01-10','2025-01-24'),(2,2,1,'2025-02-02','2025-02-20'),
  (3,1,2,'2025-02-11',NULL),(4,3,3,'2025-03-01','2025-03-15'),(5,4,3,'2025-03-18','2025-04-02'),
  (6,1,4,'2025-04-05','2025-04-19'),(7,5,4,'2025-04-21',NULL),(8,2,5,'2025-05-02','2025-05-30'),
  (9,7,1,'2025-05-14','2025-05-28'),(10,3,5,'2025-06-03',NULL),(11,6,2,'2025-06-11','2025-06-25'),
  (12,4,4,'2025-06-20','2025-07-04');
SQL

İki Sonuç Kümesi

Örnekler boyunca iki sorgu kullanılacak. Birincisi 1980 öncesi basılmış kitapların yazarlarını, ikincisi merkez şubedeki kitapların yazarlarını veriyor.

sqlite3 kutuphane.db <<'SQL'
.headers on
.mode column
SELECT yazar FROM kitap WHERE basim_yili < 1980 ORDER BY yazar;
SELECT yazar FROM kitap WHERE sube_id = 1 ORDER BY yazar;
SQL

yazar            
-----------------
Jorge Luis Borges
Oğuz Atay        
Oğuz Atay        
Yakup Kadri      
yazar        
-------------
José Saramago
Oğuz Atay    

Birinci küme dört satır ve içinde bir yineleme var — aynı yazarın iki kitabı da 1980 öncesi. İkinci küme iki satır. Ortak değer Oğuz Atay.

İki sorgunun küme işlemiyle birleştirilebilmesi için birleşim uyumlu (union compatible) olmaları gerekir: sütun sayıları eşit ve karşılıklı sütunların tipleri karşılaştırılabilir olmalı. Sayı eşit değilse sorgu çalışmaz.

sqlite3 kutuphane.db <<'SQL'
.headers on
.mode column
SELECT baslik FROM kitap
UNION
SELECT ad, sehir FROM sube;
SQL

Parse error near line 3: SELECTs to the left and right of UNION do not have the same number of result columns

Sonuçtaki sütun adları ilk sorgudan alınır; sonraki sorguların takma adları göz ardı edilir.

Birleşim ve Yinelenen Satırlar

Birleşim iki kümenin satırlarını alt alta koyar. İki yazımı vardır ve aralarındaki tek fark yinelenen satırlara ne olduğudur.

sqlite3 kutuphane.db <<'SQL'
.headers on
.mode column
SELECT yazar FROM kitap WHERE basim_yili < 1980
UNION
SELECT yazar FROM kitap WHERE sube_id = 1
ORDER BY yazar;
SQL

yazar            
-----------------
Jorge Luis Borges
José Saramago    
Oğuz Atay        
Yakup Kadri      

Dört artı iki, altı satır bekleniyordu; dört satır döndü. UNION yinelenen satırları eler — hem iki küme arasındaki ortak satırı, hem de tek bir kümenin içindeki yinelemeyi. Oğuz Atay üç kez geçtiği halde bir kez göründü.

sqlite3 kutuphane.db <<'SQL'
.headers on
.mode column
SELECT yazar FROM kitap WHERE basim_yili < 1980
UNION ALL
SELECT yazar FROM kitap WHERE sube_id = 1
ORDER BY yazar;
SQL

yazar            
-----------------
Jorge Luis Borges
José Saramago    
Oğuz Atay        
Oğuz Atay        
Oğuz Atay        
Yakup Kadri      

UNION ALL hiçbir şey elemedi: altı satır. Bu ayrım yalnız bir sonuç farkı değil, bir maliyet farkıdır. Yineleme elemek için motorun bütün satırları karşılaştırması gerekir — sıralama ya da karma tablosu kurar. UNION ALL böyle bir iş yapmaz.

Seçim açık olmalıdır: yinelemenin gerçekten elenmesi gerekiyorsa UNION, gerekmiyorsa UNION ALL. Ayrık oldukları bilinen iki kümeyi UNION ile birleştirmek, hiçbir işe yaramayan bir eleme maliyeti öder. Ters yönde, yinelemenin elenmesi gerekirken UNION ALL yazmak sessizce şişmiş sayımlar üretir.

Kesişim ve Fark

Kesişim, iki kümede de bulunan satırları verir.

sqlite3 kutuphane.db <<'SQL'
.headers on
.mode column
SELECT yazar FROM kitap WHERE basim_yili < 1980
INTERSECT
SELECT yazar FROM kitap WHERE sube_id = 1;
SQL

yazar    
---------
Oğuz Atay

Fark, birinci kümede olup ikincide olmayan satırları verir.

sqlite3 kutuphane.db <<'SQL'
.headers on
.mode column
SELECT yazar FROM kitap WHERE basim_yili < 1980
EXCEPT
SELECT yazar FROM kitap WHERE sube_id = 1;
SQL

yazar            
-----------------
Jorge Luis Borges
Yakup Kadri      

Fark işlemi yönlüdür: sıra değiştirildiğinde sonuç değişir. Kesişim ve birleşim yönsüzdür.

İki işlem de öntanımlı olarak yinelenen satırları eler. Yinelemeyi koruyan biçimleri standartta tanımlıdır ama desteği motora göre değişir.

sqlite3 kutuphane.db <<'SQL'
.headers on
.mode column
SELECT yazar FROM kitap WHERE basim_yili < 1980
INTERSECT ALL
SELECT yazar FROM kitap WHERE sube_id = 1;
SQL

Parse error near line 3: near "ALL": syntax error
  azar FROM kitap WHERE basim_yili < 1980 INTERSECT ALL SELECT yazar FROM kitap 
                                      error here ---^

Fark işleminin adı da motora göre değişir: bazı motorlarda EXCEPT yerine başka bir anahtar sözcük kullanılır. Taşınabilirlik gerekiyorsa küme işlemleri, adları ve seçenekleriyle birlikte hedef motorda sınanmalıdır.

Boş Değer Küme İşlemlerinde Eşittir

Beşinci derste ölçülen tuzak hatırlanmalı: NOT IN listesindeki tek bir boş değer sorguyu sıfır satıra düşürüyordu. Küme işlemleri aynı tuzağı taşımaz, çünkü boş değerleri gruplamanın yaptığı gibi birbirine eşit sayarlar.

sqlite3 kutuphane.db <<'SQL'
.headers on
.mode box
SELECT NULL AS birlesim UNION SELECT NULL;
SELECT NULL AS birlesim_tumu UNION ALL SELECT NULL;
SQL

┌──────────┐
│ birlesim │
├──────────┤
│          │
└──────────┘
┌───────────────┐
│ birlesim_tumu │
├───────────────┤
│               │
│               │
└───────────────┘

Çerçeveli çıktı biçimi burada satır sayısını görünür kılıyor: iki boş değer birleşimde tek satıra indi, UNION ALL yazımında iki satır kaldı. Boş değer, karşılaştırma işleci açısından kendisine eşit değildir ama küme işlemleri açısından eşittir.

Bunun doğrudan bir sonucu var: “hangi şubede hiç kitap yok” sorusu fark işlemiyle yazıldığında, kitap tablosundaki boş şube kimliği sonucu bozmaz.

sqlite3 kutuphane.db <<'SQL'
.headers on
.mode column
SELECT sube_id FROM sube
EXCEPT
SELECT sube_id FROM kitap;
SQL

sube_id
-------
4      

Doğru yanıt tek satır. Aynı soru NOT IN ile yazılsaydı, kitap tablosundaki boş kimlik yüzünden hiçbir satır dönmeyecekti. Fark işlemi, boş değerli sütunlarda NOT IN yazımına göre daha güvenli bir seçenektir.

Öncelik ve Sıralama

Birden çok küme işlecinin aynı deyimde kullanılması durumunda değerlendirme sırası önem kazanır. Standart, kesişime birleşim ve farktan daha yüksek öncelik verir. Uygulaması motora göre değişir.

sqlite3 kutuphane.db <<'SQL'
.headers on
.mode column
SELECT yazar FROM kitap WHERE sube_id = 1
UNION
SELECT yazar FROM kitap WHERE basim_yili < 1980
INTERSECT
SELECT yazar FROM kitap WHERE basim_yili > 1980;
SQL

yazar        
-------------
José Saramago

Bu motor işleçleri soldan sağa uyguladı: önce birleşim, sonra kesişim. Kesişim önce uygulansaydı ikinci ve üçüncü küme ortak satır taşımadığı için boş kalır, birleşim ilk kümeyi olduğu gibi döndürür ve sonuç iki satır olurdu. Ayraçla gruplamak da her yerde kabul edilmez.

sqlite3 kutuphane.db <<'SQL'
.headers on
.mode column
SELECT yazar FROM kitap WHERE sube_id = 1
UNION
(SELECT yazar FROM kitap WHERE basim_yili < 1980
 INTERSECT
 SELECT yazar FROM kitap WHERE basim_yili > 1980);
SQL

Parse error near line 3: near "(": syntax error
  SELECT yazar FROM kitap WHERE sube_id = 1 UNION (SELECT yazar FROM kitap WHERE
                                    error here ---^

Sonuç olarak, farklı küme işleçlerini tek deyimde karıştırmak taşınabilir bir yazım değildir. Karışık kullanım gerektiğinde ara sonuçları adlandıran yapılar — İleri SQL kursundaki ortak tablo ifadeleri — hem sırayı belirginleştirir hem de sorguyu okunur kılar.

Sıralamada da bir kural var: ORDER BY yan tümcesi tek tek sorgulara değil, birleşik sonucun tamamına uygulanır ve deyimin sonunda tek kez yazılır. Bu yüzden sıralama anahtarı, ilk sorgunun ürettiği sütun adlarıyla anılır.

Özet

• Birleştirme satırları yan yana, küme işlemleri alt alta ekler; küme işlemleri sütun sayısını değiştirmez.
• Küme işlemine giren sorgular birleşim uyumlu olmalıdır; sütun adları ilk sorgudan alınır.
• UNION yinelenen satırları eler ve bunun için karşılaştırma maliyeti öder; UNION ALL hiçbir şey elemez.
• Kesişim ve fark öntanımlı olarak yinelemeleri eler; yinelemeyi koruyan biçimlerin ve fark işlecinin adının desteği motora göre değişir.
• Küme işlemleri boş değerleri birbirine eşit sayar; bu, fark işlemini NOT IN yazımına göre daha güvenli kılar.
• Farklı küme işleçlerinin tek deyimdeki önceliği motora göre değişir; ORDER BY birleşik sonucun tamamına uygulanır.

Sonraki Adım

Bu konu boyunca yapılan her şey okumaydı: var olan satırlar seçildi, süzüldü, birleştirildi, özetlendi, kümelendi. Veri hep oradaydı — ilk derste bir kez yazıldı ve bir daha değiştirilmedi. Sıra veriyi ve onu tutan şemanın kendisini yazmakta. Sonraki konunun ilk dersi tablo oluşturmayı ele alır: sütun tanımları, tip seçimi ve kısıtların şemada nasıl ifade edildiği. İlişkisel Kuram kursunda kâğıt üzerinde tasarlanan bütünlük kuralları, orada çalışan bir tanıma dönüşür.