Çapraz ve Kendi Kendine Birleştirme

Önceki iki ders birleştirmeyi bir eşitlik koşulu üzerinden kurdu: soldaki satırın yabancı anahtarı, sağdaki satırın birincil anahtarına eşit. Koşul her zaman böyle olmak zorunda değil — hatta koşulun hiç olmadığı bir birleştirme de tanımlıdır.

Bu ders iki özel duruma bakar. Birincisi koşulsuz birleştirme: her satırın her satırla eşleştiği çarpım. İkincisi bir tablonun kendisiyle birleştirilmesi: aynı tablodan gelen iki satırı karşılaştıran sorgular. İkisi de kuralın istisnası değil, aynı kuralın uç durumlarıdır.

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

Kartezyen Çarpım

Çapraz birleştirme (cross join) hiçbir koşul almaz: soldaki her satır, sağdaki her satırla eşleştirilir. Sonucun satır sayısı iki tablonun satır sayılarının çarpımıdır. Kümeler kuramındaki karşılığı kartezyen çarpımdır (Cartesian product).

sqlite3 kutuphane.db <<'SQL'
.headers on
.mode column
SELECT COUNT(*) AS kitap_satiri FROM kitap;
SELECT COUNT(*) AS sube_satiri FROM sube;
SELECT COUNT(*) AS carpim FROM kitap CROSS JOIN sube;
SQL

kitap_satiri
------------
7           
sube_satiri
-----------
4          
carpim
------
28    

Yedi çarpı dört, yirmi sekiz. Çarpımın kendisi küçük bir örnekte zararsız görünür; on bin satırlı iki tabloda yüz milyon satır demektir. Bu, sorgunun yanlış yazılmasıyla ortaya çıkabilecek en pahalı sonuçtur ve İleri SQL kursunda sorgu planı okunurken ilk aranan belirtilerden biridir.

Sonucu görünür bir boyutta tutmak için sağ taraf süzülebilir.

sqlite3 kutuphane.db <<'SQL'
.headers on
.mode column
SELECT s.ad AS sube, k.baslik
FROM sube AS s
CROSS JOIN kitap AS k
WHERE k.yazar = 'Oğuz Atay'
ORDER BY s.sube_id, k.kitap_id;
SQL

sube          baslik           
------------  -----------------
Merkez        Tutunamayanlar   
Merkez        Tehlikeli Oyunlar
Bahçelievler  Tutunamayanlar   
Bahçelievler  Tehlikeli Oyunlar
Kadıköy       Tutunamayanlar   
Kadıköy       Tehlikeli Oyunlar
Konak         Tutunamayanlar   
Konak         Tehlikeli Oyunlar

Dört şube, iki kitap, sekiz satır. Hiçbir satır “bu kitap bu şubede” demiyor — çapraz birleştirme bir olgu değil, bir olasılık listesi üretir. Kullanışlı olduğu yer de budur: her şube ile her kitabın olası her bileşimini kurup, gerçekleşenleri dış birleştirmeyle üzerine bindirmek, boş hücreleri de görünür kılan tam bir rapor ızgarası verir.

Kazara Çarpım

Çapraz birleştirme çoğunlukla kasten yazılmaz; birleştirme koşulu unutulduğunda ortaya çıkar. Virgülle ayrılmış eski yazımda koşul WHERE yan tümcesine bırakıldığı için bu unutma özellikle kolaydır.

sqlite3 kutuphane.db <<'SQL'
.headers on
.mode column
SELECT COUNT(*) AS kosulsuz FROM kitap, sube;
SQL

kosulsuz
--------
28      

Sonuç, açıkça yazılmış çapraz birleştirmeyle aynı: yirmi sekiz. Sorgu hata vermedi. Bir önceki derste aynı iki tablonun koşullu birleştirmesi altı satır döndürüyordu.

Belirti tanınabilir: beklenenden çok daha fazla satır, tekrar eden değerler ve toplamların katlanması. Önlem de yazımsaldır — JOIN … ON biçimi kullanıldığında koşul birleştirmenin yanında durur ve unutulduğunda göze çarpar. CROSS JOIN yazımı da kasıtlı olduğunu belgeler; sorguyu okuyan kişi çarpımın niyet olduğunu görür.

Kendi Kendine Birleştirme

Bir tablo kendisiyle de birleştirilebilir. Bunun için tabloya iki farklı takma ad verilir; motor açısından bu, iki ayrı tablo gibidir. Yazımın adı kendi kendine birleştirmedir (self join) ve ayrı bir birleştirme türü değildir — iç ya da dış birleştirmenin, iki tarafında aynı tablonun bulunduğu bir örneğidir.

Aynı yazarın kitap çiftlerini bulan sorgu, önce koşulsuz haliyle yazılsın.

sqlite3 kutuphane.db <<'SQL'
.headers on
.mode column
SELECT a.baslik AS a_kitap, b.baslik AS b_kitap
FROM kitap AS a
JOIN kitap AS b ON a.yazar = b.yazar
ORDER BY a.kitap_id, b.kitap_id;
SQL

a_kitap            b_kitap          
-----------------  -----------------
Körlük             Körlük           
Tutunamayanlar     Tutunamayanlar   
Tutunamayanlar     Tehlikeli Oyunlar
Kum Kitabı         Kum Kitabı       
Yaban              Yaban            
Sessiz Ev          Sessiz Ev        
Anayurt Oteli      Anayurt Oteli    
Tehlikeli Oyunlar  Tutunamayanlar   
Tehlikeli Oyunlar  Tehlikeli Oyunlar

Dokuz satırın yalnız ikisi anlamlı, o ikisi de aynı çiftin iki yönü. Kendi kendine birleştirmenin iki tipik kusuru burada görünüyor. Birincisi, her satır kendisiyle eşleşiyor — yazarı kendine eşit olduğu için. İkincisi, gerçek çiftler iki kez, yönleri ters çevrilmiş olarak çıkıyor.

İkisi de tek bir koşulla giderilir: eşleştirmeyi birincil anahtarın sırasına bağlamak.

sqlite3 kutuphane.db <<'SQL'
.headers on
.mode column
SELECT a.yazar, a.baslik AS ilk, b.baslik AS ikinci
FROM kitap AS a
JOIN kitap AS b ON a.yazar = b.yazar AND a.kitap_id < b.kitap_id
ORDER BY a.kitap_id;
SQL

yazar      ilk             ikinci           
---------  --------------  -----------------
Oğuz Atay  Tutunamayanlar  Tehlikeli Oyunlar

a.kitap_id < b.kitap_id koşulu iki işi birden yapıyor. Eşitlik dışlandığı için satır kendisiyle eşleşemiyor; sıralama tek yönlü olduğu için her çift bir kez çıkıyor. Kesin eşitsizlik yerine <> yazılsaydı yalnız birinci sorun çözülür, aynalı yinelemeler kalırdı.

Aynı kalıp başka bir soruya uygulanabilir: aynı şehirdeki şube çiftleri.

sqlite3 kutuphane.db <<'SQL'
.headers on
.mode column
SELECT a.sehir, a.ad AS sube_1, b.ad AS sube_2
FROM sube AS a
JOIN sube AS b ON a.sehir = b.sehir AND a.sube_id < b.sube_id;
SQL

sehir   sube_1  sube_2      
------  ------  ------------
Ankara  Merkez  Bahçelievler

Kendi Kendine Birleştirmeyi Zincirlemek

Kendi kendine birleştirme başka birleştirmelerle birlikte kullanılabilir. Aynı kitabı ödünç almış üye çiftlerini bulmak için ödünç tablosu kendisiyle, sonuç ise üye ve kitap tablolarıyla birleştirilir.

sqlite3 kutuphane.db <<'SQL'
.headers on
.mode column
SELECT k.baslik, u1.ad AS uye_1, u2.ad AS uye_2
FROM odunc AS o1
JOIN odunc AS o2 ON o1.kitap_id = o2.kitap_id AND o1.uye_id < o2.uye_id
JOIN uye AS u1   ON u1.uye_id = o1.uye_id
JOIN uye AS u2   ON u2.uye_id = o2.uye_id
JOIN kitap AS k  ON k.kitap_id = o1.kitap_id
ORDER BY k.kitap_id, u1.uye_id, u2.uye_id;
SQL

baslik          uye_1   uye_2 
--------------  ------  ------
Körlük          Ayşe    Mehmet
Körlük          Ayşe    Emre  
Körlük          Mehmet  Emre  
Tutunamayanlar  Ayşe    Selin 
Kum Kitabı      Zeynep  Selin 
Yaban           Zeynep  Emre  

Beş tablo başvurusu var ama tablo sayısı üç: ödünç tablosu iki kez, üye tablosu iki kez geçiyor. Takma adlar burada isteğe bağlı değil, zorunludur — hangi uye_id sütunundan söz edildiğini başka hiçbir şey belirleyemez.

Körlük üç satır üretti çünkü üç ayrı üye almış: üç öğeden seçilen ikili sayısı üçtür. Kendi kendine birleştirmenin satır sayısı, gruptaki eleman sayısının karesiyle büyür; büyük gruplarda bu, çapraz birleştirme kadar pahalı olabilir.

Özet

• Çapraz birleştirme koşulsuzdur; satır sayısı iki tablonun satır sayılarının çarpımıdır.
• Çarpım bir olgu listesi değil olasılık listesi üretir; rapor ızgarası kurmakta işe yarar.
• Birleştirme koşulu unutulduğunda kazara çarpım hata vermeden oluşur; belirtisi beklenenden çok satır ve katlanan toplamlardır.
• Kendi kendine birleştirme ayrı bir tür değildir; aynı tabloya iki takma adla başvurmaktır ve takma ad zorunludur.
• Koşulsuz kendi kendine birleştirme her satırı kendisiyle ve her çifti iki yönde eşleştirir; birincil anahtar üzerinde kesin eşitsizlik ikisini birden giderir.
• Çift üreten birleştirmelerin maliyeti grup büyüklüğünün karesiyle artar.

Sonraki Adım

Buraya kadarki bütün sorgular satır düzeyinde kaldı: her sonuç satırı, kaynak satırların bir bileşimine karşılık geliyordu. Oysa “kaç kitap”, “ortalama ödünç süresi”, “en eski basım” gibi sorular satırları değil, satır kümelerini özetler. Sonraki ders satır kümesini tek değere indirgeyen toplama işlevlerini kurar ve bu işlevlerin boş değerleri neden saymadığını ölçer.