İlişkili Alt Sorgular

Önceki dersin son örneğinde iki alt sorgu yan yana duruyordu. Biri tablonun tamamına bakıp tek bir değer üretiyordu; diğeri dış sorgunun u.id sütununa başvuruyordu. Yazımda aradaki fark tek bir sütun adıydı, ancak bu başvuru alt sorgunun ne zaman değerlendirileceğini değiştirir.

Dış sorgunun bir sütununa başvuran alt sorguya ilişkili alt sorgu (correlated subquery) denir. Bu ders, ilişkinin ne anlama geldiğini, iş miktarını nasıl büyüttüğünü ve aynı soruyu daha az işle soran yazımın nasıl kurulduğunu ele alır.

Bağın Anlamı

Bağımsız bir alt sorgu tek başına çalıştırılabilir; sonucu dış sorgudan bağımsızdır. Bu yüzden motor onu bir kez hesaplayıp sabit gibi kullanabilir.

İlişkili alt sorgu tek başına çalıştırılamaz: içinde çözülemeyen bir ad vardır. Anlamı “dış sorgu her satırı ürettiğinde, o satırın değerleriyle yeniden değerlendirilen sorgu” biçiminde tanımlanır. Kavramsal değerlendirme sırası şudur:

1. Dış sorgu bir aday satır üretir.
2. Alt sorgudaki dış sütun başvuruları o satırın değerleriyle değiştirilir.
3. Alt sorgu çalıştırılır.
4. Sonuç, dış satırın süzülmesinde ya da bir sütununda kullanılır.

Bu tanım anlamsaldır: sorgunun ne ürettiğini belirler. Motorun bunu gerçekten satır satır çalıştırıp çalıştırmadığı ayrı bir sorudur ve dersin sonunda ele alınır.

İlişkili alt sorgunun tipik işi, “her grubun kendi içindeki uç değeri” gibi grup başına karşılaştırma gerektiren sorulardır. “Her üyenin en son aldığı kitap” sorusu böyledir:

sqlite3 -box -header <<'SQL'
CREATE TABLE uye(id INTEGER PRIMARY KEY, ad TEXT, sube_id INT);
CREATE TABLE odunc(id INTEGER PRIMARY KEY, kitap_id INT, uye_id INT, alis TEXT, iade TEXT);
INSERT INTO uye VALUES (1,'Ayse',4),(2,'Burak',4),(3,'Ceren',5),(4,'Deniz',5);
INSERT INTO odunc VALUES
  (1,1,1,'2024-03-01','2024-03-15'),(2,3,1,'2024-03-01','2024-03-20'),
  (4,7,1,'2024-03-11',NULL),(6,2,2,'2024-03-01','2024-03-12'),
  (9,8,2,'2024-03-21',NULL),(10,1,3,'2024-03-04','2024-03-10');

SELECT o.id, o.uye_id, o.alis
FROM odunc o
WHERE o.alis = (SELECT MAX(i.alis) FROM odunc i WHERE i.uye_id = o.uye_id)
ORDER BY o.uye_id;
SQL

┌────┬────────┬────────────┐
│ id │ uye_id │    alis    │
├────┼────────┼────────────┤
│ 4  │ 1      │ 2024-03-11 │
│ 9  │ 2      │ 2024-03-21 │
│ 10 │ 3      │ 2024-03-04 │
└────┴────────┴────────────┘

İçteki odunc tablosuna i, dıştakine o takma adı verilmesi zorunludur: aynı tablo iki farklı rolde geçtiği için uye_id sütununun hangisine ait olduğu ancak takma adla belirlenir. Bağı kuran satır WHERE i.uye_id = o.uye_id koşuludur; o içeriden görülebildiği için alt sorgu ilişkilidir.

İş Miktarını Ölçmek

Satır başına değerlendirmenin maliyeti, sayılarak gösterilebilir. Bunun için sorguya bir sayaç yerleştirilir: her çağrıldığında bir değişkeni artıran ve argümanını olduğu gibi döndüren gecis adlı bir kullanıcı işlevi. İşlev iç tablonun koşuluna konur; böylece iç tablonun taranan her satırında bir kez çalışır ve toplam çağrı sayısı, iç tabloya kaç satır dokunulduğunu verir.

Aynı sonucu üreten iki yazım karşılaştırılır: ilişkili alt sorgu ve iç tabloyu bir kez gruplayıp birleştiren yazım.

cat > olcum.mjs <<'JS'
import { DatabaseSync } from 'node:sqlite';

const db = new DatabaseSync(':memory:');
db.exec(`
  CREATE TABLE uye(id INTEGER PRIMARY KEY, ad TEXT);
  CREATE TABLE odunc(id INTEGER PRIMARY KEY, uye_id INT, alis TEXT);
  INSERT INTO uye VALUES (1,'Ayse'),(2,'Burak'),(3,'Ceren'),(4,'Deniz'),
                         (5,'Emre'),(6,'Fatma'),(7,'Gokhan'),(8,'Hale');
  INSERT INTO odunc VALUES
    (1,1,'2024-03-01'),(2,1,'2024-03-01'),(3,1,'2024-03-04'),(4,1,'2024-03-11'),
    (5,1,'2024-03-18'),(6,2,'2024-03-01'),(7,2,'2024-03-06'),(8,2,'2024-03-11'),
    (9,2,'2024-03-21'),(10,3,'2024-03-04'),(11,3,'2024-03-06'),(12,3,'2024-03-13'),
    (13,3,'2024-03-25'),(14,4,'2024-03-04'),(15,4,'2024-03-13'),(16,4,'2024-03-20'),
    (17,5,'2024-03-06'),(18,5,'2024-03-11'),(19,5,'2024-03-25'),(20,6,'2024-03-04'),
    (21,6,'2024-03-13'),(22,6,'2024-03-20'),(23,7,'2024-03-11'),(24,7,'2024-03-18'),
    (25,8,'2024-03-13');
`);

let ziyaret = 0;
db.function('gecis', (deger) => { ziyaret += 1; return deger; });

ziyaret = 0;
const iliskili = db.prepare(`
  SELECT u.ad, (SELECT COUNT(*) FROM odunc o WHERE gecis(o.uye_id) = u.id) AS adet
  FROM uye u ORDER BY u.id`).all();
console.log('ilişkili   :', iliskili.map((s) => s.adet).join(','), '| ziyaret:', ziyaret);

ziyaret = 0;
const birlestirmeli = db.prepare(`
  SELECT u.ad, COALESCE(s.adet, 0) AS adet
  FROM uye u
  LEFT JOIN (SELECT uye_id, COUNT(*) AS adet FROM odunc
             WHERE gecis(uye_id) IS NOT NULL GROUP BY uye_id) AS s
    ON s.uye_id = u.id
  ORDER BY u.id`).all();
console.log('birleştirme:', birlestirmeli.map((s) => s.adet).join(','),
            '| ziyaret:', ziyaret);
JS
node olcum.mjs

ilişkili   : 5,4,4,3,3,3,2,1 | ziyaret: 200
birleştirme: 5,4,4,3,3,3,2,1 | ziyaret: 25

İki yazım aynı sekiz sayıyı üretti. İç tabloya dokunma sayısı ise 200’e karşı 25.

Sayı şaşırtıcı değildir: dış tabloda 8, iç tabloda 25 satır var. İlişkili yazımda iç tablo her dış satır için baştan sona taranır, yani $8 \times 25 = 200$. Birleştirmeli yazımda iç tablo bir kez taranıp gruplanır, yani 25; ardından sekiz satırlık iki küçük küme eşleştirilir.

Genel olarak, dış tabloda $m$, iç tabloda $n$ satır varsa ilişkili yazım $O(m \cdot n)$, önce gruplayıp sonra birleştiren yazım $O(m + n)$ iş yapar. Üye sayısı ile işlem sayısının birlikte on katına çıktığı bir kütüphanede, ilk yazımın işi yüz katına, ikincisinin işi on katına çıkar.

Sorgu Planında Görmek

Ölçüm, motorun ne yaptığını dışarıdan gösterir. Motorun ne yapmayı planladığı ise sorgu planından okunur. EXPLAIN QUERY PLAN, sorguyu çalıştırmadan planı yazdırır:

sqlite3 <<'SQL'
CREATE TABLE uye(id INTEGER PRIMARY KEY, ad TEXT, sube_id INT);
CREATE TABLE odunc(id INTEGER PRIMARY KEY, kitap_id INT, uye_id INT, alis TEXT, iade TEXT);
.print -- dizinsiz --
EXPLAIN QUERY PLAN
SELECT u.ad, (SELECT COUNT(*) FROM odunc o WHERE o.uye_id = u.id) AS adet
FROM uye u ORDER BY u.id;
CREATE INDEX odunc_uye ON odunc(uye_id);
.print
.print -- dizinli --
EXPLAIN QUERY PLAN
SELECT u.ad, (SELECT COUNT(*) FROM odunc o WHERE o.uye_id = u.id) AS adet
FROM uye u ORDER BY u.id;
SQL

-- dizinsiz --
QUERY PLAN
|--SCAN u
`--CORRELATED SCALAR SUBQUERY 1
   `--SCAN o

-- dizinli --
QUERY PLAN
|--SCAN u
`--CORRELATED SCALAR SUBQUERY 1
   `--SEARCH o USING COVERING INDEX odunc_uye (uye_id=?)

Plandaki CORRELATED SCALAR SUBQUERY satırı, alt sorgunun ilişkili olduğunu ve tek değer döndürdüğünü söyler. Altındaki satır iç tabloya nasıl erişildiğini verir: dizin yokken SCAN o — yani baştan sona tarama — dizin varken SEARCH o USING … INDEX — yani doğrudan erişim. Ölçülen 200 sayısı, ilk plandaki taramanın sekiz kez yinelenmesidir.

İkinci plan, ilişkili alt sorgunun kendiliğinden pahalı olmadığını gösterir: pahalı olan, uygun erişim yolu olmadan yinelenen taramadır. Dizin varken her yineleme tam tarama değil, tek bir arama olur ve toplam iş satır sayısıyla değil, eşleşme sayısıyla orantılı hâle gelir. Planın nasıl okunacağı ve dizinlerin bu tabloyu nasıl değiştirdiği, kursun Sorgu Başarımı konusunun asıl işidir.

Motorun Yeniden Yazma Hakkı

Bir sorgu ne yapılacağını söyler, nasıl yapılacağını değil. Planlayıcı, sonucu değiştirmemek koşuluyla ilişkili alt sorguyu birleştirmeye ya da yarı birleştirmeye çevirebilir; iç sonucu bir kez maddeleştirip yeniden kullanabilir. Bu dönüşümlere alt sorgu azaltma denir ve motora göre kapsamı değişir.

Sonuç iki yönlüdür. Bir yandan, ilişkili yazımı gördüğü her yerde “bu yavaştır” demek yanlıştır — motor kurtarmış olabilir. Öte yandan, kurtarmanın garanti olmadığı da doğrudur: alt sorguda toplama işlevi, LIMIT, boş değer duyarlı bir koşul ya da yan etkili bir işlev varsa dönüşüm anlamı bozacağı için yapılmaz. Ölçmeden varsayım yürütmemek, bu yüzden bir alışkanlıktır.

EXISTS ile Birleştirmenin Farkı

İlişkili alt sorgunun ikinci yaygın biçimi, değer değil varlık soran EXISTS yüklemidir. Aynı soru birleştirmeyle de yazılabilir, ancak ikisi eşdeğer değildir:

sqlite3 -box -header <<'SQL'
CREATE TABLE uye(id INTEGER PRIMARY KEY, ad TEXT, sube_id INT);
CREATE TABLE odunc(id INTEGER PRIMARY KEY, kitap_id INT, uye_id INT, alis TEXT, iade TEXT);
INSERT INTO uye VALUES (1,'Ayse',4),(2,'Burak',4),(3,'Ceren',5),(4,'Deniz',5);
INSERT INTO odunc VALUES
  (4,7,1,'2024-03-11',NULL),(9,8,2,'2024-03-21',NULL),
  (13,2,3,'2024-03-25',NULL),(16,4,3,'2024-03-20',NULL);

SELECT u.ad FROM uye u
WHERE EXISTS (SELECT 1 FROM odunc o WHERE o.uye_id = u.id AND o.iade IS NULL)
ORDER BY u.id;

SELECT u.ad FROM uye u
JOIN odunc o ON o.uye_id = u.id AND o.iade IS NULL
ORDER BY u.id;
SQL

┌───────┐
│  ad   │
├───────┤
│ Ayse  │
│ Burak │
│ Ceren │
└───────┘
┌───────┐
│  ad   │
├───────┤
│ Ayse  │
│ Burak │
│ Ceren │
│ Ceren │
└───────┘

Ceren ikinci sonuçta iki kez görünür, çünkü iade edilmemiş iki kaydı vardır ve birleştirme her eşleşme için bir satır üretir. EXISTS ise “en az bir eşleşme var mı” sorusunu sorar; ilk eşleşmede durur ve satırı çoğaltmaz.

Bu fark, birleştirmeli yeniden yazımın sınırıdır: satır sayısını değiştirmeyen bir dönüşüm isteniyorsa birleştirmenin sonucuna DISTINCT ya da gruplama eklemek gerekir. Bu eklemeler de bedava değildir; sıralama ya da geçici yapı maliyeti getirir. EXISTS ile yazılmış bir yüklemi birleştirmeye çevirirken önce eşleşmenin tekil olup olmadığı sorulmalıdır.

Özet

• İlişkili alt sorgu, dış sorgunun bir sütununa başvurur ve anlamsal olarak dış satır başına yeniden değerlendirilir.
• Dış tabloda $m$, iç tabloda $n$ satır için yinelenen tarama $O(m \cdot n)$, önce gruplayıp birleştiren yazım $O(m + n)$ iş yapar; ölçümde bu 200’e karşı 25 olarak çıktı.
• EXPLAIN QUERY PLAN çıktısındaki CORRELATED SCALAR SUBQUERY satırı ilişkiyi, altındaki SCAN ya da SEARCH satırı erişim yolunu gösterir.
• İlişkili yazım kendiliğinden pahalı değildir; uygun erişim yolu varsa her yineleme tam tarama yerine tek aramaya iner.
• EXISTS satırı çoğaltmaz, birleştirme çoğaltır; iki yazım ancak eşleşme tekilse aynı sonucu verir.

Sonraki Adım

Bu dersteki birleştirmeli yazım, ara sonucu FROM içine gömülü bir alt sorgu olarak kurdu. İç içe iki üç katman sonra bu yazım okunmaz hâle gelir: ara sonucun adı sorgunun ortasında kaybolur ve aynı ara sonuç iki yerde gerekiyorsa tekrar yazılır. Sonraki ders, ara sonuçları sorgunun başında adlandırıp gövdeyi sade bırakan ortak tablo ifadelerini tanıtacak.