JOIN Tipleri ve SQL Tablo Birleştirme İşlemleri

Bu yazı içerisinde, örneklendirmeyi SQL ile vermiş olmamın nedeni R, Tableau ve komut satırı (command line) örneklerinden daha kolay pratik edilebilecek ortama sahip olması. Böylelikle, temel mantık diğer uygulamalarda da (ilişkisel veritabanı bağlamında) benzer olduğu için herhangi bir karmaşaya sebebiyet vermemiş olacağımı varsayıyorum. O halde, hızlıca konuya girelim.

JOIN

İlişkisel veritabanı (relational database) sistemlerinde sıklıkla kullanılan işlemlerden biri olan JOIN, table ve view gibi kaynakları ilişkilendiren, SELECT, INSERT INTO SELECT, SELECT INTO, UPDATE ve DELETE gibi işlemlerde FROM ile birlikte kullanılan ve bu şekilde tabloları (iki veya daha fazla) birleştiren ve sorgular temelinde bir sonuç tablosu (result table) oluşturmaya yarayan bir birleştiricidir. Bu işlemlerde, gereksinime göre farklı özelliklere sahip birleştiricilerden biri kullanılır; JOIN tipleri başlığı altında bu tiplerle ilgili detaylara ulaşabilirsiniz. Az önceki tanımlamada da bahsi geçtiği üzere JOIN birleştirmenin (sonuç tablosu oluşturma) yanı sıra SELECT ile iki ya da daha fazla tablo ilişkilendirilerek sorgulama yapılabilir. Ayrıca, SELF JOIN ile aynı tabloda da sorgulama yapmak mümkündür. Basit bir JOIN işlemi ile yukarıdaki tanımı örneklendirelim.

SELECT * FROM [table1] JOIN [table2] ON [table1.primary_key] = [table2.foreign_key];

Örneğe baktığımızda table1 ve table2 tablolarını ve bu tablolara ait primary_key ile foreign_key anahtarlarını görmekteyiz. Key, Primary Key, Unique Key ve Foreign Key Tanımlamaları başlıklı yazımda birincil (primary key) ve ikincil (foreign key) anahtarlarla ilgili tanımlalara yer vermiştim. Örneğe geri dönecek olursak, birleştirici olan kullanılan JOIN ile hangi tabloların birleştirileceğini belirtiyoruz; table1 ve table2. ON ifadesi ise bu tabloların hangi alanlar bağlamında ilişkilendirileceğini belirtir; table1.primary_key ile table2.foreign_key. Unutmatan, bu sütunların aldığı değerlerin (primary_key ve foreign_key) aynı veri tipine sahip olmaları gerekir. Tablo arasındaki işlemlerde genellikle = veya <> operatörleri ve yazım biçimini okunabilir ve pratik hale getirmek için alias kullanılır.

JOIN Tipleri

JOIN türlerini birleştirilen tablolardan hangisinin baskın olacağı veya hangisinden hangi alanların okunacağını belirtir ve bu amaçla tipler dahilinde çeşitli farklılıklar gösterirler. Aşağıda JOIN tipleriyle ilgili bilgilerin yanı sıra, bir durum senaryosu da ele alacağım ve bu sayede veriler üzerinde gerçek işlemler yapmış olacağız. Örnek işlemler için sqlfiddle veya farklı bir alternatif üzerinden ilerleyebilirsiniz¹.

CREATE TABLE `attendance` (
  `attendance_ID` INT(11),
  `course_ID` INT(11),
  `employee_ID` INT(11),
  `courseDate` DATE
);

INSERT INTO `attendance` (`attendance_ID`, `course_ID`, `employee_ID`, `courseDate`) VALUES
(1, 1, 1, '2018-12-11'),
(2, 1, 2, '2018-12-12'),
(3, 2, 1, '2018-12-15'),
(4, 2, 2, '2018-12-12');

CREATE TABLE `courses` (
  `course_ID` INT(11),
  `course` VARCHAR(100),
  `courseDate` DATE,
  `department` VARCHAR(100)
);

INSERT INTO `courses` (`course_ID`, `course`, `courseDate`, `department`) VALUES
(1, 'Motivation', '2018-12-11', 'IT'),
(2, 'Hardware', '2018-12-22', 'HR');

CREATE TABLE `employee` (
  `employee_ID` INT(11),
  `name` VARCHAR(100),
  `surname` VARCHAR(100),
  `recordingDate` DATE,
  `offDay` INT(11),
  `workDay` INT(11),
  `department` VARCHAR(100),
  `status` VARCHAR(100)
);

INSERT INTO `employee` (`employee_ID`, `name`, `surname`, `recordingDate`, `offDay`, `workDay`, `department`, `status`) VALUES
(1, 'Georgie', 'Truax', '2018-12-04', 3, 20, 'IT', '1'),
(2, 'Ben', 'Killam', '2018-11-05', 14, 50, 'ART', '1'),
(3, 'Ronnie', 'Mehler', '2017-10-09', 32, 240, 'HR', '1'),
(4, 'Bertha', 'Raby', '2017-12-19', 22, 300, 'ART', '0');

CREATE TABLE `instructors` (
  `instructor_ID` INT(11),
  `name` VARCHAR(100),
  `surname` VARCHAR(100),
  `course_ID` INT(11)
);

INSERT INTO `instructors` (`instructor_ID`, `name`, `surname`, `course_ID`) VALUES
(1, 'Delena', 'Wigley', 1),
(2, 'Ken', 'Soluri', 2);

Yukarıdaki SQL komutlarını uyguladığınızda şu tabloları elde edersiniz:

attendance

courses

employee

instructors

INNER JOIN (İç Birleştirici)

Varsayılan seçenektir ve birebir ilişki için tercih edilir. İki veya daha fazla veri tablosunu belirtilen kolon veya kolonların eşitliğine göre ortak kolonlar üzerinden birleştirir. Kullanımında JOIN ifadesinin yanı sıra INNER JOIN şeklinde de tercih edilebilir. O halde, ilk işlemimizi gerçekleştirelim.

SELECT E.* FROM employee E INNER JOIN courses C ON E.department=C.department

Yukarıdaki JOIN kullanımı bize eğitimlere katılan çalışanları (employee) departman (department) ilişkisi üzerinden listeleyecektir. E.* bize tüm alanları sunacaktır. İlgili tanımlamayı belirli alanlarla sınırlandırmak için E.deparment gibi bir kullanım da tercih edebilirdik. Bu durumda sadece departman kolonu listelenecektir.

Şimdide bu işlemi kolon tanımlaması olmadan 3 tabloyu birleştirerek gerçekleştirelim.

SELECT * FROM courses C INNER JOIN employee E ON C.department=E.department INNER JOIN instructors I ON I.course_ID=C.course_ID

OUTER JOIN (Dış Birleştirici)

OUTER JOIN temelde INNER JOIN ile benzer şekilde ortak kolonlar üzerinden bağlantı kurar, ancak sonuç olarak tablolarda karşılığı olmayan satırları getirir. Bu sayede bir veri tablosunda olup bir diğerinde (veya diğerlerinde) olmayan kayıtları listeleyebiliriz. Kullanım aşamasında baskın olacak (temel olarak alınacak) tablo ayrıca LEFT ve RIGHT ifadeleriyle belirtilir ve belirtilen ikinci tabloda karşılığı olmayan satırlar NULL olarak gösterilir.

LEFT OUTER JOIN veya LEFT JOIN

Dış birleştiricilerden LEFT COIN solunda belirtilen tablodaki tüm satırları listeler ve sağ tarafta belirtilen tabloya ait kolonları ise NULL olarak döndürür. Hemen bir örnek gerçekleştirelim.

SELECT * FROM employee E LEFT JOIN courses C ON E.department=C.department

Bu SQL komutunu uyguladığımızda employee ve course kolonlarının birleştirilerek bize döndürüldüğünü ancak, eşleşme sağlanmayan alanların, yani herhangi bir kursa katılmayanların NULL değerini aldığını görebilirsiniz.

Aşağıda ayrıca 3 örnek daha iletiyorum. Sonuçları karşılaştırmak isterseniz bu konutları uygulayarak çıktıları değerlendirebilirsiniz.

SELECT * FROM employee E LEFT JOIN courses C ON E.department=C.department WHERE E.status=0 ORDER BY E.employee_ID ASC
SELECT * FROM courses C LEFT JOIN employee E ON C.department=E.department WHERE E.status=1 ORDER BY E.employee_ID ASC
SELECT * FROM employee E LEFT JOIN courses C ON E.department=C.department WHERE E.status=1 AND C.course IS NULL ORDER BY E.employee_ID ASC

RIGHT OUTER JOIN veya RIGHT JOIN

RIGHT JOIN, LEFT JOIN gibi yön belirtrerek sağında yer alan tablodaki tüm satırları listeler. Solunda yer alan tablo NULL olarak dönmektedir. Örneğimizi yukarıdaki örneklerden birini düzenleyerek oluşturacağım;

SELECT * FROM courses C RIGHT JOIN employee E ON C.department=E.department WHERE E.status=1 ORDER BY E.employee_ID ASC

Komutun uygulanmasının ardından herhangi bir kursa katılmayan çalışanın kurs bilgilerinin NULL olarak belirtildiğini görebilirsiniz.

FULL JOIN veya FULL OUTER JOIN

FULL JOIN, arasında belirtildiğitablolara ait (sağ ve sol) tüm satırları listeler ve bu tablolarda karşılığı olmayan satırları NULL olarak belirtir. FULL JOIN işlemi LEFT JOIN ve RIGHT JOIN işlemlerinin UNION ile birleştirilmesi şeklinde de uygulanabilir. Örneğin;

SELECT * FROM employee E LEFT JOIN attendance A ON E.employee_ID=A.employee_ID UNION SELECT * FROM employee E RIGHT JOIN courses C ON E.recordingDate=C.courseDate

CROSS JOIN (Çapraz Birleştirici)

Fazla kullanılmayan bir yöntemdir. Tablolar arasında yapılan birleştirmede seçilen kolonlar arasındaki tüm kombinasyonlar sonuç tablosu (result table) haline getirilir. Bu sonuç tablosundaki satır sayısı alanların kartezyen çarpımı (cartesian product / iki kümenin elemanlarının sırayla karşılaştırılması) kadardır. Örneğin, aşağıdaki işlem tüm kurslar için tüm eğitmenleri listeler.

SELECT * FROM courses C CROSS JOIN instructors I

SELF JOIN (Kendi Kendine Birleştirici)

Bir veri tablosunun kendisi ile birleştirilmesidir ve çok kullanılan bir yöntem değildir. Bir veri tablosundaki satırlar arasında referans ilişki olması durumunda o tablo için alias kullanılarak birleştirme işlemleri gerçekleştirilebilir. Bu şekilde tek bir tablo bağımsız tablolar gibi gösterilebilir.

UNION

FULL JOIN (FULL OUTER JOIN başlığı altında kısaca değindiğim UNION işleminden de ayrıca bahsetmek uygun olacaktır. UNION, birleştirme tiplerinin dışında, birden fazla tabloda sorgu gerçekleştirmek amacıyla kullanılan yöntemlerden biridir. Yukarıdaki örnekte birden fazla SELECT ifadesini tek bir sorgu olarak uygulamıştık, bu sayede tek sorgulama ile sonuç tablolar yaratılabilmektedir. UNION işleminde birleştirilmek istenen veri tablolarının belirlenen alanlar (kolonlar) aynı sayıda ve aynı veri tipinde olmalıdır. Alanların belirlenmesi işlemi SELECT ile sağlanır. Aynı satırdan birden fazla olsa dahi sonuç tablosunda oluşabilecek satırları göstermek için UNION ALL kullanılır.

Daha fazla örnek kullanım için MariaDB > Joining Tables with JOIN Clauses² ve More Advanced Joins³ yazılarını inceleyebilirsiniz. Yukarıda, aşağıda yer alan görseli JOIN tiplerinin işlevlerine dair bir özet olarak değerlendirebilirsiniz. İlgili görselin düzenlenebilir AI (Adobe Illustrator) versiyonunu buradan indirebilirsiniz.

İleri Okumalar

• JOIN Relationships and JOINing Tables
• İç / Dış Birleştirme (Inner / Outer Anti Semi Join)
• SQL 5 : Tablo Birleştirme ve Join Komutları
• MySQL INNER JOIN
• SQL 18: Join Türleri
• SQL JOIN
• SQL Joins