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.
İ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]; |
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 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); |
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_ID | course_ID | employee_ID | courseDate |
|---|
| 1 | 1 | 1 | 2018-12-11 |
| 2 | 1 | 2 | 2018-12-12 |
| 3 | 2 | 1 | 2018-12-15 |
| 4 | 2 | 2 | 2018-12-12 |
attendance
| course_ID | course | courseDate | department |
|---|
| 1 | Motivation | 2018-12-11 | IT |
| 2 | Hardware | 2018-12-22 | HR |
courses
| employee_ID | name | surname | recordingDate | offDay | workDay | department | status |
|---|
| 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 |
employee
| instructor_ID | name | surname | course_ID |
|---|
| 1 | Delena | Wigley | 1 |
| 2 | Ken | Soluri | 2 |
instructors
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 |
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_ID | name | surname | recordingDate | offDay | workDay | department | status |
|---|
| 1 | Georgie | Truax | 2018-12-04 | 3 | 20 | IT | 1 |
| 3 | Ronnie | Mehler | 2017-10-09 | 32 | 240 | HR | 1 |
Ş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 |
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_ID | course | courseDate | department | employee_ID | name | surname | recordingDate | offDay | workDay | department | status | instructor_ID | name | surname | course_ID |
|---|
| 1 | Motivation | 2018-12-11 | IT | 1 | Georgie | Truax | 2018-12-04 | 3 | 20 | IT | 1 | 1 | Delena | Wigley | 1 |
| 2 | Hardware | 2018-12-22 | HR | 3 | Ronnie | Mehler | 2017-10-09 | 32 | 240 | HR | 1 | 2 | Ken | Soluri | 2 |
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.
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 |
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_ID | name | surname | recordingDate | offDay | workDay | department | status | course_ID | course | courseDate | department |
|---|
| 1 | Georgie | Truax | 2018-12-04 | 3 | 20 | IT | 1 | 1 | Motivation | 2018-12-11 | IT |
| 3 | Ronnie | Mehler | 2017-10-09 | 32 | 240 | HR | 1 | 2 | Hardware | 2018-12-22 | HR |
| 2 | Ben | Killam | 2018-11-05 | 14 | 50 | ART | 1 | NULL | NULL | NULL | NULL |
| 4 | Bertha | Raby | 2017-12-19 | 22 | 300 | ART | 0 | NULL | NULL | NULL | NULL |
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 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 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 |
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 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 |
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, 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 |
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
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 |
SELECT * FROM courses C CROSS JOIN instructors I
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.
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:
