SQLite JOIN Türleri: INNER, LEFT, RIGHT, FULL ve CROSS JOIN

Örnek veri

Yazarlar ve kitaplar tablosunda bilerek iki "kenar durum" bıraktık: Halide adlı yazarın hiç kitabı yok, Anonim Antoloji adlı kitabın ise yazarı yok (yazar_id NULL). Her birleştirme türünün bu iki satıra nasıl davrandığı, türler arasındaki farkı en iyi gösteren şeydir.

CREATE TABLE yazar(id INTEGER PRIMARY KEY, ad TEXT);
CREATE TABLE kitap(id INTEGER PRIMARY KEY, baslik TEXT, yazar_id INTEGER, yil INTEGER);
INSERT INTO yazar VALUES (1, 'Orhan'), (2, 'Sabahattin'), (3, 'Halide');
INSERT INTO kitap VALUES
  (10, 'Kar',              1,    2002),
  (11, 'Masumiyet Müzesi', 1,    2008),
  (12, 'Kuyucaklı Yusuf',  2,    1937),
  (13, 'Anonim Antoloji',  NULL, 1990);

Birleştirme nasıl çalışır: kartezyen çarpım ve süzme

SQLite belgeleri bütün birleştirmeleri kavramsal olarak aynı temele oturtur: sol ve sağ veri kümesinin kartezyen çarpımı. Sol tarafta N, sağ tarafta M satır varsa çarpımda N x M satır vardır ve her satır, sol tarafın bütün sütunlarını sağ tarafın bütün sütunları izleyecek biçimde içerir. ON koşulu bu çarpımın her satırı için hesaplanır ve yalnızca koşulun doğru olduğu satırlar kalır. Dış birleştirmeler bunun üzerine bir adım ekler: eşleşmeyen satırlar için diğer tarafın sütunları NULL ile doldurulmuş ek satırlar üretilir.

Bu tarif kavramsaldır; SQLite gerçekte koca bir çarpım tablosu oluşturmaz. Sorgu planlayıcı birleştirmeyi iç içe döngülerle uygular ve uygun indeksleri kullanarak her döngüde yalnızca gereken satırları okur. Ancak sonucun ne olacağını düşünürken "önce bütün eşleşmeler, sonra koşul, sonra eksik satırlar" sırası her zaman doğru yanıtı verir.

INNER JOIN: yalnızca eşleşenler

İç birleştirme, koşulu sağlayan satır çiftlerini döndürür. Halide'nin kitabı olmadığı ve Anonim Antoloji'nin yazarı olmadığı için ikisi de sonuçta yer almaz. SQLite'ta JOIN, INNER JOIN ve virgül arasında hiçbir fark yoktur; belgeler bunların tamamen birbirinin yerine kullanılabileceğini belirtir. Virgülle yazdığınızda koşulu WHERE bölümüne koyarsınız ve sonuç aynıdır.

SELECT y.ad, k.baslik
  FROM yazar AS y
  JOIN kitap AS k ON k.yazar_id = y.id
 ORDER BY k.id;
-- çıktı:
-- Orhan|Kar
-- Orhan|Masumiyet Müzesi
-- Sabahattin|Kuyucaklı Yusuf

-- Aynı sorgu, virgüllü birleştirme ve WHERE ile
SELECT y.ad, k.baslik FROM yazar y, kitap k WHERE k.yazar_id = y.id ORDER BY k.id;
-- çıktı:
-- Orhan|Kar
-- Orhan|Masumiyet Müzesi
-- Sabahattin|Kuyucaklı Yusuf

Tablolara AS y veya kısaca y gibi takma adlar vermek, sütun adları iki tabloda da geçtiğinde hangi sütunu kastettiğinizi belirtmenin en kısa yoludur. İç birleştirmede bir koşulu ON veya WHERE içine yazmanız sonucu değiştirmez; fark yalnızca dış birleştirmelerde ortaya çıkar.

LEFT JOIN: sol tablonun bütün satırları

LEFT JOIN (veya LEFT OUTER JOIN) önce iç birleştirmenin sonucunu üretir, ardından sağ tarafta hiçbir eşleşmesi olmayan her sol satır için bir satır daha ekler. Eklenen satırlarda sağ tablonun sütunları NULL'dır. Halide bu yüzden sonuçta boş bir kitap başlığıyla görünür.

SELECT y.ad, k.baslik
  FROM yazar y
  LEFT JOIN kitap k ON k.yazar_id = y.id
 ORDER BY y.id, k.id;
-- çıktı:
-- Orhan|Kar
-- Orhan|Masumiyet Müzesi
-- Sabahattin|Kuyucaklı Yusuf
-- Halide|

-- Hiç kitabı olmayan yazarlar
SELECT y.ad FROM yazar y LEFT JOIN kitap k ON k.yazar_id = y.id WHERE k.id IS NULL;
-- çıktı: Halide

İkinci sorgu çok kullanılan bir kalıptır: dış birleştirmeden sonra sağ tablonun NULL olamayacak bir sütununu (burada birincil anahtar k.id) IS NULL ile sınayarak "eşi olmayan" satırları bulursunuz. Kontrol için birincil anahtar gibi gerçekten NULL olamayan bir sütun seçin; NULL değer alabilen bir sütun seçerseniz eşleşmiş ama o sütunu boş olan satırları da yanlışlıkla yakalarsınız.

ON ile WHERE arasındaki kritik fark

Dış birleştirmede NULL ile doldurulan ek satırlar ON koşulu işlendikten sonra, WHERE koşulu işlenmeden önce eklenir. Belgelerin özellikle vurguladığı bu sıra şu sonucu doğurur: sağ tabloya ait bir koşulu ON içine yazarsanız yalnızca eşleşmeyi daraltırsınız ve sol tablonun bütün satırları korunur. Aynı koşulu WHERE içine yazarsanız NULL ile doldurulmuş satırlarda koşul NULL üretir ve bu satırlar elenir.

-- Koşul ON içinde: bütün yazarlar kalır
SELECT y.ad, k.baslik
  FROM yazar y
  LEFT JOIN kitap k ON k.yazar_id = y.id AND k.yil > 2005
 ORDER BY y.id;
-- çıktı:
-- Orhan|Masumiyet Müzesi
-- Sabahattin|
-- Halide|

-- Koşul WHERE içinde: eşleşmeyen satırlar elenir
SELECT y.ad, k.baslik
  FROM yazar y
  LEFT JOIN kitap k ON k.yazar_id = y.id
 WHERE k.yil > 2005
 ORDER BY y.id;
-- çıktı: Orhan|Masumiyet Müzesi

İkinci sorgu artık fiilen bir iç birleştirmedir. SQLite'ın sorgu iyileştiricisi bunu da fark eder: belgelerdeki "OUTER JOIN strength reduction" bölümüne göre WHERE koşulu sağ tablodaki bir sütunun NULL olmamasını gerektiriyorsa LEFT JOIN sıradan bir birleştirmeye indirgenebilir. Kısacası, sağ tabloyu süzmek ama sol tablonun tamamını korumak istiyorsanız koşul ON içine yazılır.

RIGHT JOIN ve FULL JOIN

RIGHT JOIN, LEFT JOIN işleminin aynasıdır: sağ tablonun eşleşmeyen satırları korunur ve sol tablonun sütunları NULL ile doldurulur. FULL JOIN (veya FULL OUTER JOIN) ikisini birleştirir; hem soldaki hem sağdaki eşleşmeyen satırlar sonuca eklenir. Bu iki tür SQLite 3.39.0 sürüm notu ile geldi; daha eski bir kütüphane kullanıyorsanız sözdizimi hatası alırsınız.

SELECT y.ad, k.baslik FROM yazar y RIGHT JOIN kitap k ON k.yazar_id = y.id ORDER BY k.id;
-- çıktı:
-- Orhan|Kar
-- Orhan|Masumiyet Müzesi
-- Sabahattin|Kuyucaklı Yusuf
-- |Anonim Antoloji

SELECT y.ad, k.baslik FROM yazar y FULL JOIN kitap k ON k.yazar_id = y.id ORDER BY y.id, k.id;
-- çıktı:
-- |Anonim Antoloji
-- Orhan|Kar
-- Orhan|Masumiyet Müzesi
-- Sabahattin|Kuyucaklı Yusuf
-- Halide|

FULL JOIN sonucunda Anonim Antoloji'nin en başta görünmesi, sıralamanın y.id ile yapılması ve NULL'ın en küçük değer kabul edilmesinden kaynaklanıyor. RIGHT JOIN yazmak yerine tabloların yerini değiştirip LEFT JOIN yazmak da aynı sonucu verir. Belgeler taşınabilirlik için LEFT JOIN tercih etmeyi önerir; sorgu iyileştiricinin bazı dönüşümleri de şimdilik yalnızca LEFT JOIN için uygulanıyor (gereksiz dış birleştirmenin sorgudan tamamen atılması gibi).

Dış birleştirme ile sayım yaparken

Her yazarın kaç kitabı olduğunu sayarken count(*) kullanmak yaygın bir hatadır. count(*) gruptaki satırları sayar ve LEFT JOIN eşleşmeyen yazar için de bir satır ürettiğinden Halide'ye 1 kitap yazar. count(k.id) ise yalnızca NULL olmayan değerleri saydığı için doğru sonucu, 0'ı verir.

SELECT y.ad, count(k.id) AS kitap_sayisi
  FROM yazar y LEFT JOIN kitap k ON k.yazar_id = y.id
 GROUP BY y.id, y.ad ORDER BY y.id;
-- çıktı:
-- Orhan|2
-- Sabahattin|1
-- Halide|0

SELECT y.ad, count(*) AS kitap_sayisi
  FROM yazar y LEFT JOIN kitap k ON k.yazar_id = y.id
 GROUP BY y.id, y.ad ORDER BY y.id;
-- çıktı:
-- Orhan|2
-- Sabahattin|1
-- Halide|1

Gruplama ve sayma fonksiyonlarının NULL ile ilişkisi GROUP BY ve aggregate fonksiyonlar yazısında ayrıntılı olarak anlatılıyor.

USING ve NATURAL JOIN

İki tabloda birleştirme sütununun adı aynıysa ON a.x = b.x yerine USING (x) yazabilirsiniz. Belgelere göre USING içindeki her sütun iki tarafta da bulunmak zorundadır ve her biri için sol.x = sag.x karşılaştırması yapılır. Karşılaştırmada tip eğilimi, harmanlama ve NULL için normal kurallar geçerlidir. NATURAL ise sizin yerinize örtük bir USING ekler: iki tabloda adı aynı olan bütün sütunlar bu listeye girer. Ortak ad yoksa NATURAL sonucu hiç etkilemez.

CREATE TABLE siparis(siparis_id INTEGER PRIMARY KEY, musteri TEXT);
CREATE TABLE kargo(siparis_id INTEGER, firma TEXT);
INSERT INTO siparis VALUES (1, 'Ayşe'), (2, 'Mehmet'), (3, 'Zeynep');
INSERT INTO kargo VALUES (1, 'Hızlı Kargo'), (3, 'Ekspres'), (4, 'Hızlı Kargo');
.headers on

SELECT * FROM siparis JOIN kargo ON kargo.siparis_id = siparis.siparis_id;
-- çıktı:
-- siparis_id|musteri|siparis_id|firma
-- 1|Ayşe|1|Hızlı Kargo
-- 3|Zeynep|3|Ekspres

SELECT * FROM siparis JOIN kargo USING (siparis_id);
-- çıktı:
-- siparis_id|musteri|firma
-- 1|Ayşe|Hızlı Kargo
-- 3|Zeynep|Ekspres

SELECT * FROM siparis NATURAL JOIN kargo;
-- çıktı:
-- siparis_id|musteri|firma
-- 1|Ayşe|Hızlı Kargo
-- 3|Zeynep|Ekspres

SELECT * FROM siparis FULL JOIN kargo USING (siparis_id) ORDER BY siparis_id;
-- çıktı:
-- siparis_id|musteri|firma
-- 1|Ayşe|Hızlı Kargo
-- 2|Mehmet|
-- 3|Zeynep|Ekspres
-- 4||Hızlı Kargo
.headers off

Çıktıdaki sütun başlıklarına dikkat edin. ON kullanıldığında siparis_id iki kez görünür. USING ve NATURAL ile her ortak sütun bir kez yer alır: iç ve sol birleştirmede sol tablonun sütunu kullanılır, sağ birleştirmede sağ tablonunki. FULL JOIN için belgeler iki sütunun da atılıp yerine coalesce(sol.x, sag.x) değerinin konduğunu söyler; bu yüzden 4 numaralı, siparişi olmayan kargo kaydı da anahtar değeriyle görünüyor.

Kendi kendine birleştirme (self join)

Bir tablonun kendisiyle birleştirilmesi, aynı tablodaki satırlar arasındaki ilişkileri okumanın yoludur. Bunun için tabloyu iki farklı takma adla FROM bölümüne koyarsınız. Aşağıdaki örnekte her çalışanın yöneticisinin adını getiriyoruz; yöneticisi olmayan Elif'i kaybetmemek için LEFT JOIN kullandık.

CREATE TABLE personel(id INTEGER PRIMARY KEY, ad TEXT, yonetici_id INTEGER);
INSERT INTO personel VALUES (1, 'Elif', NULL), (2, 'Burak', 1), (3, 'Selin', 1), (4, 'Kerem', 2);

SELECT c.ad AS calisan, y.ad AS yonetici
  FROM personel c
  LEFT JOIN personel y ON y.id = c.yonetici_id
 ORDER BY c.id;
-- çıktı:
-- Elif|
-- Burak|Elif
-- Selin|Elif
-- Kerem|Burak

Bu yöntem tek seviyelik ilişkiler için yeterlidir. Bir çalışanın bütün üst zincirini veya bir kategorinin bütün alt kategorilerini derinliği bilinmeyen bir ağaçta bulmanız gerekiyorsa özyinelemeli ortak tablo ifadesi kullanmalısınız; bkz. SQLite WITH ve recursive CTE.

CROSS JOIN ve birleştirme sırası

CROSS JOIN, koşul olmadan yazıldığında iki tablonun kartezyen çarpımını üretir: 3 yazar ile 4 kitap 12 satır verir. Belgelere göre CROSS JOIN sonuç olarak iç birleştirmeyle aynıdır, ancak sorgu planlayıcısı açısından özel bir anlamı vardır: SQLite bir CROSS JOIN ifadesindeki tabloların sırasını hiçbir zaman değiştirmez. Sol tablo her zaman dış döngü olur. Bu, planlayıcının seçtiği sırayı elle zorlamanın bir yoludur ve SQLite'a özgüdür.

Normal iç birleştirmelerde SQLite, indeksleri en iyi kullanacak döngü sırasını kendisi seçer. Belgeler CROSS JOIN ile elle müdahaleyi son çare olarak görür ve önce PRAGMA optimize ile istatistiklerin güncel tutulmasını önerir. Konunun ayrıntısı ANALYZE ve sorgu planlayıcı yazısında. Dış birleştirmeler ise hiçbir zaman yeniden sıralanmaz, çünkü sıralarını değiştirmek sonucu değiştirir.

CREATE TABLE t1(a); CREATE TABLE t2(b); CREATE TABLE t3(c);
INSERT INTO t1 VALUES (1), (2);
INSERT INTO t2 VALUES (1);
INSERT INTO t3 VALUES (1), (3);

SELECT a, b, c FROM t1 LEFT JOIN t2 ON b = a LEFT JOIN t3 ON c = a ORDER BY a;
-- çıktı:
-- 1|1|1
-- 2||

-- ON yazılmamış dış birleştirme koşulsuzdur
SELECT a, b FROM t1 LEFT JOIN t2 ORDER BY a;
-- çıktı:
-- 1|1
-- 2|1

SELECT count(*) FROM yazar CROSS JOIN kitap;
-- çıktı: 12

İkiden fazla tablo birleştirildiğinde işlemler soldan sağa yapılır: A LEFT JOIN B LEFT JOIN C önce A ile B'yi, sonra bu sonucu C ile birleştirir. Örnekteki ikinci sorgu ise SQLite'ın esnekliğini gösteriyor: standart SQL'de bir dış birleştirmenin ON veya USING içermesi zorunludur, SQLite buna izin verir. Koşulsuz bir dış birleştirme ON true yazılmış gibi davranır ve her sol satır sağ tablonun her satırıyla eşleşir.

SQLite'ın standart dışı birleştirme kuralları

SQLite birleştirme sözdiziminde oldukça hoşgörülüdür. Belgelere göre JOIN sözcüğünden önce CROSS, FULL, INNER, LEFT, NATURAL, OUTER ve RIGHT sözcüklerinden bir ile üç tanesini herhangi bir sırayla yazabilirsiniz; örneğin LEFT RIGHT JOIN, FULL JOIN ile aynı anlama gelir. Çelişkili birleşimler (INNER OUTER JOIN gibi) reddedilir. SQLite'ın gerçekten uyguladığı yalnızca iki kısıt vardır: NATURAL birleştirmeye ON veya USING eklenemez ve aynı birleştirmede ikisi birden kullanılamaz.

SELECT ad, baslik FROM yazar JOIN kitap ON yazar_id = yazar.id WHERE id = 1;
-- hata: ambiguous column name: id

SELECT ad FROM yazar NATURAL JOIN kitap ON 1;
-- hata: a NATURAL join may not have an ON or USING clause

İlk hata da sık görülür: id sütunu iki tabloda da bulunduğu için SQLite hangisini kastettiğinizi bilemez. Sütunu yazar.id biçiminde tablo adıyla nitelemeniz gerekir.

Bir başka fark işleç önceliğidir. Standart SQL'de JOIN sözcüğüyle yazılan birleştirmeler virgüllü birleştirmelerden önce işlenir; SQLite'ta ise bütün birleştirmeler aynı önceliğe sahiptir ve soldan sağa işlenir. Sadece iç birleştirme kullanıyorsanız bu fark sonucu etkilemez. Belgeler taşınabilir sorgular için şu kuralları önerir: virgüllü birleştirmeyi JOIN sözcüğüyle karıştırmayın, dış birleştirmelerde LEFT JOIN tercih edin ve emin olmadığınızda sırayı parantezlerle belirtin.

JOIN yerine EXISTS ne zaman?

Bazen amacınız diğer tablodan sütun almak değil, yalnızca orada bir eşleşme olup olmadığını öğrenmektir. Bu durumda birleştirme, bir siparişin birden çok kargo kaydı varsa satırı çoğaltır. EXISTS alt sorgusu ise her dış satır için yalnızca 0 veya 1 üretir ve satır sayısını değiştirmez. NOT EXISTS de "eşi olmayanları bul" sorusunun LEFT JOIN ... IS NULL kalıbına bir alternatifidir.

SELECT s.musteri FROM siparis s
 WHERE EXISTS (SELECT 1 FROM kargo k WHERE k.siparis_id = s.siparis_id)
 ORDER BY 1;
-- çıktı:
-- Ayşe
-- Zeynep

SELECT s.musteri FROM siparis s
 WHERE NOT EXISTS (SELECT 1 FROM kargo k WHERE k.siparis_id = s.siparis_id);
-- çıktı: Mehmet

Belgelere göre EXISTS alt sorgusunun döndürdüğü sütunların sayısı ve değerleri sonucu etkilemez; bu yüzden alt sorguda SELECT 1 yazmak yaygındır. Dış sorgudaki bir sütuna başvuran bu tür alt sorgulara ilişkili (correlated) alt sorgu denir ve sonucu her gerektiğinde yeniden hesaplanır. NOT IN ile aynı soruyu sorarken alt sorgu NULL döndürebiliyorsa beklenmedik biçimde hiç satır alamayabilirsiniz; bu tuzak SQLite NULL davranışı yazısında örnekleniyor.

Tür karşılaştırması

Sık Sorulan Sorular

JOIN ile INNER JOIN arasında fark var mı?

Hayır. SQLite'ta JOIN, INNER JOIN ve virgül aynı sonucu verir ve planlayıcı tarafından aynı biçimde ele alınır. Tek istisna CROSS JOIN'dur: sonuç aynıdır ama tablo sırası yeniden düzenlenmez.

Birleştirme sorgum yavaş, ne yapmalıyım?

Önce ON koşulundaki sütunlarda, özellikle iç döngüde aranan tarafta bir indeks olup olmadığını kontrol edin. Planı EXPLAIN QUERY PLAN ile görebilirsiniz. Genel sorgu iyileştirme ilkeleri için SQL sorgu optimizasyonu yazısına bakın.

Kaç tabloyu birleştirebilirim?

Belgelere göre SQLite'ın planlayıcısı 50 veya 60 tabloluk birleştirmeleri bile çok kısa sürede planlayabilir. Pratikte sınırı sorgunun okunabilirliği belirler; çok tablolu sorguları ortak tablo ifadeleriyle parçalara ayırmak bakımı kolaylaştırır.

Eski bir SQLite'ta FULL JOIN yerine ne kullanırım?

3.39.0 öncesinde bir LEFT JOIN ile tabloların yeri değiştirilmiş ikinci bir LEFT JOIN'i UNION ile birleştirerek aynı sonuca ulaşabilirsiniz. Yeni bir projede ise güncel bir SQLite sürümü kullanmak daha basittir.

Marka adları sahiplerine aittir. Bu yazı bağımsız bir teknik rehberdir; adı geçen kuruluşlarla bayilik veya ortaklık ilişkisi bulunduğu anlamına gelmez.

İlgili içerikler: SQLite öğrenme rehberi · SQLite SELECT · GROUP BY ve aggregate · SQLite foreign key · SQLite index oluşturma · SQLite NULL davranışı