Data

JOIN Tipleri ve SQL Tablo Birleştirme İşlemleri

Yayın:
Veri birleştirme (data blending) işlemleri öncesinde Key, Primary Key, Unique Key ve Foreign Key tanımlamaları ile başlayan yazılara JOIN, JOIN tipleri ve tablo birleştirme işlemleriyle devam ediyorum.
GÖRSEL
Veri birleştirme (data blending) işlemleri öncesinde Key, Primary Key, Unique Key ve Foreign Key Tanımlamaları ile başlayan ön yazılara JOIN kullanımı ve tablo birleştirme işlemleriyle devam ediyorum. 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 Nedir? Nasıl Kullanılır?

İ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.com 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_IDcourse_IDemployee_IDcourseDate
1112018-12-11
2122018-12-12
3212018-12-15
4222018-12-12
attendance
course_IDcoursecourseDatedepartment
1Motivation2018-12-11IT
2Hardware2018-12-22HR
courses
employee_IDnamesurnamerecordingDateoffDayworkDaydepartmentstatus
1GeorgieTruax2018-12-04320IT1
2BenKillam2018-11-051450ART1
3RonnieMehler2017-10-0932240HR1
4BerthaRaby2017-12-1922300ART0
employee
instructor_IDnamesurnamecourse_ID
1DelenaWigley1
2KenSoluri2
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.
employee_IDnamesurnamerecordingDateoffDayworkDaydepartmentstatus
1GeorgieTruax2018-12-04320IT1
3RonnieMehler2017-10-0932240HR1
Ş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
course_IDcoursecourseDatedepartmentemployee_IDnamesurnamerecordingDateoffDayworkDaydepartmentstatusinstructor_IDnamesurnamecourse_ID
1Motivation2018-12-11IT1GeorgieTruax2018-12-04320IT11DelenaWigley1
2Hardware2018-12-22HR3RonnieMehler2017-10-0932240HR12KenSoluri2

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.
employee_IDnamesurnamerecordingDateoffDayworkDaydepartmentstatuscourse_IDcoursecourseDatedepartment
1GeorgieTruax2018-12-04320IT11Motivation2018-12-11IT
3RonnieMehler2017-10-0932240HR12Hardware2018-12-22HR
2BenKillam2018-11-051450ART1NULLNULLNULLNULL
4BerthaRaby2017-12-1922300ART0NULLNULLNULLNULL
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 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.Önerdiğim diğer kaynaklar:
Ceyhun Enki Aksan

Kullanıcı Davranışları Analizi (User Behavior Analysis) ve Kullanıcı Deneyim Tasarımı (UX Design) üzerine çalışmalar yürütmekte, bu süreçte edindiğim teknik ve pratik bilgileri fayda sağlamak motivasyonuyla (afaik / as far as i know) paylaşmaktayım.

HABERDAR OL

Yeni eklenen projeler, eğitimler, içerikler ve yayınlanan videolar e-posta adresine gelsin.