MariaDB JOIN Türleri, Alt Sorgular ve UNION, INTERSECT, EXCEPT

Örnek tablolar

Kılavuz, birleştirme türlerini iki küçük tabloyla gösterir. t1 tablosunda 1, 2 ve 3; t2 tablosunda 2 ve 4 değerleri vardır. Ortak değer yalnızca 2'dir. Bu küçük örnek, her birleştirme türünün eşleşmeyen satırlara ne yaptığını açıkça görmenizi sağlar:

CREATE TABLE t1 ( a INT );
CREATE TABLE t2 ( b INT );

INSERT INTO t1 VALUES (1), (2), (3);
INSERT INTO t2 VALUES (2), (4);

INNER JOIN ve CROSS JOIN

INNER JOIN yalnızca birleştirme koşulunu her iki tabloda da sağlayan satırları döndürür:

SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.b;
+------+------+
| a    | b    |
+------+------+
|    2 |    2 |
+------+------+
1 row in set (0.00 sec)

CROSS JOIN ise her satırı diğer tablonun her satırıyla eşleştirir; buna Kartezyen çarpım denir. Üç satır ile iki satırın çarpımı altı satır verir:

SELECT * FROM t1 CROSS JOIN t2;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    2 |    2 |
|    3 |    2 |
|    1 |    4 |
|    2 |    4 |
|    3 |    4 |
+------+------+
6 rows in set (0.00 sec)

Belge önemli bir MariaDB ayrıntısını not eder: standart SQL'de farklı olsalar da MariaDB'de CROSS JOIN ile INNER JOIN söz dizimi açısından eşdeğerdir ve birbirinin yerine yazılabilir. Fark, koşul verip vermediğinizdir: ON yazmazsanız her iki biçim de Kartezyen çarpım üretir. Büyük tablolarda unutulan bir ON, milyonlarca satırlık bir sonuç demektir.

LEFT JOIN ve RIGHT JOIN

LEFT JOIN (veya LEFT OUTER JOIN) soldaki tablonun tüm satırlarını döndürür. Sağda eşleşme yoksa sağ tablonun sütunları NULL ile doldurulur:

SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.b;
+------+------+
| a    | b    |
+------+------+
|    1 | NULL |
|    2 |    2 |
|    3 | NULL |
+------+------+
3 rows in set (0.00 sec)

RIGHT JOIN aynı mantığı sağ tablo için uygular. Tablo sırasını değiştirip LEFT JOIN yazmak aynı sonucu verir; birçok ekip okunabilirlik için yalnızca LEFT JOIN kullanmayı tercih eder:

SELECT * FROM t1 RIGHT JOIN t2 ON t1.a = t2.b;
+------+------+
| a    | b    |
+------+------+
|    2 |    2 |
| NULL |    4 |
+------+------+
2 rows in set (0.00 sec)

LEFT JOIN'in çok kullanılan bir kalıbı, karşılığı olmayan satırları bulmaktır. JOIN Syntax sayfasındaki örnek, sağ tabloda eşi olmayan sol satırları döndürür:

SELECT left_tbl.*
  FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id
  WHERE right_tbl.id IS NULL;

Kılavuz ayrıca MariaDB 12.3'ten itibaren birbirinden bağımsız birden çok LEFT JOIN işleminin, optimizer_switch içindeki reorder_outer_joins bayrağı açıksa iyileştirici tarafından yeniden sıralanabildiğini belirtir. Sorgu planını incelemek için EXPLAIN ve ANALYZE yazısına bakabilirsiniz.

ON, USING, NATURAL ve virgül

Birleştirme koşulu iki biçimde yazılır. ON kosul herhangi bir ifadeyi kabul eder. USING (sutun) ise iki tabloda aynı adı taşıyan sütun için kısayoldur; Basics Guide'daki kitap ve yazar örneği bunu kullanır:

SELECT title, name_last
FROM books
JOIN authors USING (author_id);

Sözdiziminde bir de NATURAL JOIN biçimi bulunur, ancak yerel belgeler davranışını ayrıntılı açıklamadığı için bu yazıda koşulu her zaman açıkça ON veya USING ile yazıyoruz. Eski stil virgüllü yazımda (FROM t1, t2 WHERE t1.a = t2.b) koşul WHERE içine girer. Belge bu yazımın INNER JOIN ile aynı sonucu verdiğini, ama açık JOIN sözdiziminin tercih edilmesi gerektiğini üç gerekçeyle açıklar:

• Okunabilirlik: İlişki koşulları ON içinde, iş kuralları WHERE içinde kalır.
• Esneklik: Bir INNER JOIN'i LEFT JOIN'e çevirmek tek sözcük değiştirmektir. Virgüllü yazımla karıştırıldığında ise virgül ile JOIN arasındaki öncelik farkı yüzünden ON koşulu virgülle bağlanmış tablonun sütunlarını göremez ve sorgu "Unknown column" hatasıyla durur.
• Taşınabilirlik: Standart sözdizimi başka veritabanlarına geçişi kolaylaştırır.

Üç veya daha fazla tablo, JOIN yan tümceleri art arda yazılarak birleştirilir. Comma vs JOIN sayfasındaki örnek, son iki haftada sipariş veren müşterilerin telefonlarını getirir:

SELECT *
FROM
  clients
  INNER JOIN orders ON clients.id = orders.clientId
  INNER JOIN phoneNumbers ON clients.id = phoneNumbers.clientId
WHERE
  orderPlaced >= NOW() - INTERVAL 2 WEEK;

Telefonu olmayan müşterileri de görmek isterseniz ikinci INNER JOIN'i LEFT JOIN yapmanız yeterlidir. Birleştirme sütunlarında indeks olması performans açısından belirleyicidir; konu indeks oluşturma yazısında.

JOIN Syntax sayfası, aynı birleştirme sözdiziminin yalnızca SELECT için değil, çok tablolu UPDATE ve DELETE ifadeleri için de geçerli olduğunu belirtir. Yani bir tablodaki satırları başka bir tablodaki eşleşmelere göre güncellerken veya silerken burada öğrendiğiniz INNER JOIN ve LEFT JOIN kalıplarını aynen kullanırsınız; örnekler INSERT, UPDATE ve DELETE yazısında. Tablo adından sonra USE INDEX, FORCE INDEX veya IGNORE INDEX ipuçları da yazılabilir; bunlar iyileştiricinin hangi indeksi kullanacağını etkiler.

Uygulamalı örnek: JOIN ile WHERE birlikte

Advanced Joins sayfası küçük bir iş yerinin Employees ve Hours tablolarıyla ilerler: ilk tablo çalışanları, ikincisi her çalışanın hafta boyunca giriş ve çıkış saatlerini tutar ve iki tablo ID sütunuyla bağlanır. Bir çalışanın geç kalıp kalmadığını kontrol etmek için önce iki tablo INNER JOIN ile birleştirilir, ardından WHERE ile yalnızca o çalışanın satırları süzülür:

SELECT
  `Employees`.`First_Name`,
  `Employees`.`Last_Name`,
  `Hours`.`Clock_In`,
  `Hours`.`Clock_Out`
FROM `Employees`
INNER JOIN `Hours` ON `Employees`.`ID` = `Hours`.`ID`
WHERE `Employees`.`First_Name` = 'Helmholtz';

Belgede bu sorgu o çalışanın beş günlük kaydını döndürür. Sonraki adımda WHERE koşuluna tarih aralığı ve saat için DATE_FORMAT() ile yapılan karşılaştırmalar eklenerek yalnızca 07:00:59 sonrasındaki girişler bırakılır. Bu örnekteki önemli ders, birleştirme koşulunun (ON) tabloların nasıl ilişkilendiğini, WHERE koşulunun ise hangi kayıtların istendiğini anlatmasıdır; iki görevi ayrı tutmak, sorgu büyüdükçe okunabilirliği korur. Tarih fonksiyonları tarih ve saat fonksiyonları yazısında anlatılıyor.

Alt sorgular

Skaler alt sorgu

Tek satır ve tek sütun döndüren alt sorgu, bir sabitin yazılabileceği çoğu yerde kullanılabilir. Belgeye göre alt sorgu hiç satır döndürmezse sonuç NULL olur; birden fazla satır döndürürse sorgu hata verir:

INSERT INTO sq1 VALUES (2);

INSERT INTO sq2 VALUES (10* (SELECT num FROM sq1));
ERROR 1242 (21000): Subquery returns more than 1 row

LIMIT gibi yalnızca sabit kabul eden yerlerde alt sorgu kullanılamaz. WHERE içinde en sık görülen biçim şudur:

SELECT * FROM sq1 WHERE num = (SELECT MAX(num)/10 FROM sq2); 
+------+
| num  |
+------+
|    1 |
+------+

IN ve EXISTS

sutun IN (SELECT ...), belgeye göre = ANY (SELECT ...) ile aynıdır. EXISTS ise alt sorgu en az bir satır döndürüyorsa doğru olur; alt sorgunun seçim listesine bakılmaz, sonuç asla NULL olmaz. Genellikle dış sorgunun sütununa başvuran ilişkili (correlated) alt sorguyla kullanılır:

SELECT * FROM sq1 WHERE EXISTS (SELECT * FROM sq2 WHERE num2 > num);
+------+
| num  |
+------+
|   30 |
+------+

Belgenin özellikle uyardığı fark NULL ile ilgilidir: NOT IN ve NOT EXISTS, karşılaştırılan sütunlarda NULL yokken aynı sonucu verir, ama NULL varsa farklılaşabilir, çünkü NOT IN bir NULL karşısında bilinmeyen sonucuna düşer. Bu iki sorgu da şu LEFT JOIN ile yeniden yazılabilir:

SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.col1=table2.col1 
       WHERE table2.col1 IS NULL;

Alt sorgu sınırlamaları sayfasına göre IN, ALL, ANY veya SOME ile kullanılan bir alt sorguda LIMIT desteklenmez; denendiğinde ERROR 1235 (42000) hatası döner. Bu kısıt ORDER BY için geçerli değildir.

FROM içinde alt sorgu (türetilmiş tablo)

FROM içindeki alt sorguya türetilmiş tablo (derived table) denir ve bir takma adla adlandırılması zorunludur. İç içe toplama fonksiyonları doğrudan yazılamadığında işe yarar. Belgedeki öğrenci tablosunda her öğrencinin toplam puanlarının ortalamasını bulmak için AVG(SUM(score)) yazmak ERROR 1111 (HY000): Invalid use of group function verir; türetilmiş tabloyla ise çalışır:

SELECT AVG(sq_sum) FROM (SELECT SUM(score) AS sq_sum FROM student GROUP BY name) AS t;
+-------------+
| AVG(sq_sum) |
+-------------+
|    134.0000 |
+-------------+

MariaDB 11.7'den itibaren türetilmiş tablonun sütunlarına takma addan sonra parantez içinde ad verilebilir: (SELECT ...) AS dt (a1, a2, a3).

Alt sorgu mu, JOIN mi?

Subqueries and JOINs sayfası, alt sorguların çoğu zaman ama her zaman değil, bir JOIN olarak yeniden yazılabildiğini söyler. IN içeren bir alt sorgu, DISTINCT ile birleştirmeye çevrilebilir; LEFT JOIN olarak yazılabilen alt sorgular bazen daha verimlidir. Öte yandan alt sorgunun tek doğru araç olduğu durumlar da vardır: sol tablodaki tekrarları koruyup sağ tablodaki tekrarların sonucu çoğaltmasını istemiyorsanız IN ile yazılan alt sorgu doğru cevabı verir, düz JOIN fazladan satır, DISTINCT ise eksik satır üretir. En dıştaki ifade bir UPDATE olduğunda da alt sorgu daha doğal bir çözümdür.

UNION, INTERSECT ve EXCEPT

Küme işlemleri birden çok SELECT sonucunu tek bir sonuç kümesinde toplar. Sonucun sütun adları ilk SELECT'ten alınır; aynı sıradaki sütunların aynı tipte olması beklenir, değilse sonuç tipi tüm sorguların değerleri dikkate alınarak belirlenir. UNION varsayılan olarak tekrarları atar (DISTINCT), UNION ALL korur. INTERSECT iki kümede ortak olan, EXCEPT soldaki kümede olup sağdakinde olmayan satırları verir; ikisi de varsayılan olarak DISTINCT uygular ve ALL biçimleri vardır. Belgedeki örnek dört işlemi aynı tabloda karşılaştırır:

CREATE TABLE seqs (i INT);
INSERT INTO seqs VALUES (1),(2),(2),(3),(3),(4),(5),(6);

SELECT i FROM seqs WHERE i <= 3 UNION SELECT i FROM seqs WHERE i>=3;
+------+
| i    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
+------+

SELECT i FROM seqs WHERE i <= 3 EXCEPT SELECT i FROM seqs WHERE i>=3;
+------+
| i    |
+------+
|    1 |
|    2 |
+------+

SELECT i FROM seqs WHERE i <= 3 INTERSECT SELECT i FROM seqs WHERE i>=3;
+------+
| i    |
+------+
|    3 |
+------+

SELECT i FROM seqs WHERE i <= 3 INTERSECT ALL SELECT i FROM seqs WHERE i>=3;
+------+
| i    |
+------+
|    3 |
|    3 |
+------+

Öncelik kuralı sürpriz yaratabilir: Oracle kipi dışında INTERSECT, UNION ve EXCEPT'ten önce işlenir. Niyetinizi parantezle açık yazın. Sonucun tamamına uygulanacak ORDER BY ve LIMIT en sona yazılır; sütunlara takma ad verdiyseniz ORDER BY takma adı kullanmalıdır. Tek tek SELECT'lere ait ORDER BY ve LIMIT ise ancak parantez içinde yazılabilir ve birleşik sonucun sırasını etkilemez. Belge, UNION ALL'un çoğu durumda geçici tablo oluşturmadan çalıştırılabildiğini de not eder.

Sık yapılan hatalar

• ON koşulunu unutmak. MariaDB'de koşulsuz JOIN Kartezyen çarpım üretir; satır sayısı tablo boyutlarının çarpımı olur.
• LEFT JOIN sonrasında sağ tabloyu WHERE ile süzmek. WHERE sag.sutun = 5 gibi bir koşul NULL satırları eler ve sorgu fiilen INNER JOIN'e döner; sağ tabloya ait koşulu ON içine yazın.
• Virgüllü ve açık JOIN yazımını karıştırmak. Öncelik farkı yüzünden "Unknown column" hatası alınır.
• NOT IN ile NULL içeren bir sütunu karşılaştırmak. Sonuç beklenenden az satır olabilir; NOT EXISTS veya LEFT JOIN ... IS NULL kullanın.
• Skaler alt sorgunun tek satır döndüreceğini varsaymak. Veri büyüdüğünde 1242 hatası çıkar; koşulu daraltın veya toplama fonksiyonu kullanın.
• UNION'un tekrarları koruduğunu sanmak. Varsayılan DISTINCT'tir; tüm satırlar için UNION ALL yazın.

Sık Sorulan Sorular

JOIN ile INNER JOIN aynı şey mi?

Evet. Belgedeki sözdiziminde [INNER | CROSS] JOIN isteğe bağlı sözcüklerle tanımlıdır; tek başına JOIN, INNER JOIN anlamına gelir.

Farklı veritabanlarındaki tabloları birleştirebilir miyim?

Evet. Tabloları veritabani.tablo biçiminde yazarak aynı sorguda birden çok veritabanını kullanabilirsiniz; bu kullanım hem JOIN hem UNION sayfalarında belirtilir.

STRAIGHT_JOIN ne işe yarar?

STRAIGHT_JOIN, tabloların yazıldığı sırayla birleştirilmesini isteyen bir iyileştirici ipucudur. MariaDB 12.0'dan itibaren JOIN_ORDER gibi birleştirme sırası ipuçları da vardır. İpuçlarına başvurmadan önce planı EXPLAIN ile inceleyin.

Alt sorguyu her seferinde yeniden yazmak yerine adlandırabilir miyim?

Evet, WITH ile tanımlanan ortak tablo ifadeleri (CTE) bunu sağlar ve özyinelemeli sorgulara da izin verir. Ayrıntılar CTE ve WITH RECURSIVE yazısında; satır bazlı hesaplamalar için window fonksiyonları yazısına da bakabilirsiniz.

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: MariaDB öğrenme rehberi · SELECT ve WHERE · GROUP BY · CTE · indeks oluşturma · SQL sorgu optimizasyonu