SQL'de Birleştirmeler Nasıl Kullanılır

giriiş

Birçok veritabanı tasarımı, bilgileri belirli veri noktaları arasındaki ilişkilere göre farklı tablolara ayırır. Bu gibi durumlarda bile, bazen birinin aynı anda birden fazla tablodan bilgi almak istemesi muhtemeldir.

Yapılandırılmış Sorgu Dili (SQL) işleminde birden fazla tablodan verilere erişmenin yaygın bir yolu, tabloları bir JOIN ifadesiyle birleştirmektir. İlişkisel cebirdeki birleştirme işlemine dayanan bir JOIN ifadesi, her tablodaki birbiriyle ilişkili satırları eşleştirerek ayrı tabloları birleştirir. Genellikle bu ilişki, bir tablonun yabancı anahtarı ve yabancı anahtarın başvurduğu başka bir tablonun birincil anahtarı gibi değerleri paylaşan, her tablodan birer sütun olmak üzere bir çift sütuna dayanır.

Bu kılavuz, JOIN ifadesi içeren çeşitli SQL sorgularının nasıl oluşturulacağını açıklamaktadır. Ayrıca, farklı JOIN ifadesi türlerini, birden fazla tablodan verilerin nasıl birleştirileceğini ve JOIN işlemlerini yazmayı daha az sıkıcı hale getirmek için sütunlara nasıl takma ad verileceğini de göstermektedir.

Ön koşullar

Bu kılavuzu takip etmek için, SQL kullanan bir tür ilişkisel veritabanı yönetim sistemine (RDBMS) sahip bir bilgisayara ihtiyacınız olacak. Bu kılavuzdaki talimatlar ve örnekler aşağıdaki ortam kullanılarak doğrulanmıştır:

• Yönetici ayrıcalıklarına sahip kök olmayan bir kullanıcı ve UFW ile yapılandırılmış bir güvenlik duvarı bulunan Ubuntu 20.04 çalıştıran bir sunucu
• Sunucuda MySQL kurulu ve güvenlidir.
• Ayrıca, JOIN işlemlerini pratik yapmak için kullanabileceğiniz örnek verilerle yüklü bazı tabloların bulunduğu bir veritabanına da ihtiyacınız olacak. MySQL sunucusuna nasıl bağlanacağınız ve bu kılavuzdaki örneklerde kullanılan test veritabanını nasıl oluşturacağınız hakkında ayrıntılı bilgi için aşağıdaki MySQL'e Bağlanma ve Örnek Veritabanı Oluşturma bölümünü takip etmenizi öneririz.

MySQL'e bağlanma ve örnek bir veritabanı kurma

SQL veritabanı sisteminiz uzak bir sunucuda çalışıyorsa, yerel makinenizden sunucunuza SSH ile bağlanın:

ssh sammy@your_server_ip

Daha sonra MySQL sunucusu istemini açın ve Sami'yi MySQL kullanıcı adınızla değiştirin:

mysql -u sammy -p

joinsDB adında bir veritabanı oluşturun:

CREATE DATABASE joinsDB;

Veritabanı başarıyla oluşturulduysa aşağıdaki çıktıyı alacaksınız:

Output
Query OK, 1 row affected (0.01 sec)

joinsDB veritabanını seçmek için aşağıdaki USE ifadesini çalıştırın:

USE joinsDB;

Output
Database changed

joinsDB'yi seçtikten sonra, içinde birkaç tablo oluşturun. Bu kılavuzda kullanılan örnekler için, bir fabrika işlettiğinizi ve üretim hattınız, satış ekibinizdeki çalışanlar ve şirketinizin satışları hakkındaki bilgileri bir SQL veritabanında takip etmeye karar verdiğinizi varsayalım. İlkinde ürünlerinizle ilgili bilgilerin depolanacağı üç tabloyla başlayacaksınız. Bu ilk tablonun üç sütuna ihtiyacı olduğuna karar veriyorsunuz:

• Ürün Kimliği: Her ürünün int veri türünde ifade edilen kimlik numarası. Bu sütun, tablonun birincil anahtarı görevi görür; yani her değer, ilgili satır için benzersiz bir tanımlayıcı görevi görür. Birincil anahtardaki her değer benzersiz olması gerektiğinden, bu sütuna bir UNIQUE kısıtlaması da uygulanır.
• productName: Her bir ürünün adı, en fazla 20 karakter uzunluğunda olan varchar veri tipi kullanılarak ifade edilir.
• Fiyat: Her ürünün ondalık veri türü kullanılarak ifade edilen fiyatı. Bu, bu sütundaki her değerin uzunluğunun en fazla dört basamakla sınırlı olduğunu ve bu basamaklardan ikisinin ondalık noktasının sağında olduğunu belirtir. Dolayısıyla, bu sütunda izin verilen değer aralığı -99,99 ile 99,99 arasındadır.

Şu üç sütunu içeren products adlı bir tablo oluşturun:

CREATE TABLE products (
productID int UNIQUE,
productName varchar(20),
price decimal (4,2),
PRIMARY KEY (productID)
);

İkinci tablo, şirketinizin satış ekibindeki çalışanlar hakkında bilgi içeriyor. Bu tablonun da üç sütuna ihtiyacı olduğuna karar veriyorsunuz:

• empID: productID sütununa benzer şekilde, bu sütun da satış ekibindeki her çalışan için int veri türüyle ifade edilen benzersiz bir kimlik numarası içerir. Bu nedenle, bu sütun UNIQUE kısıtlamasına sahip olacak ve ekip tablosunun birincil anahtarı olarak işlev görecektir.
• empName: Her satış elemanının varchar veri türü kullanılarak ifade edilen ve en fazla 20 karakterden oluşan adı.
• productSpecialty: Satış ekibinizin her üyesine uzmanlık alanı olarak bir ürün atanır. Şirketinizin ürettiği herhangi bir ürünü satabilirler, ancak genel olarak odaklandıkları ürün üzerindedirler. Bunu tabloda göstermek için, her çalışanın uzmanlaştığı her ürünün productID değerini içeren bu sütunu oluşturun.

productSpecialty sütununun yalnızca geçerli ürün kimlik numaralarını temsil eden değerler içermesini sağlamak için, Products tablosunun productID sütununa başvuran sütuna bir yabancı anahtar kısıtlaması uygulamaya karar verirsiniz. Yabancı anahtar kısıtlaması, uygulandığı sütundaki değerlerin başvurduğu sütunda da bulunmasını zorunlu kılarak iki tablo arasındaki ilişkiyi ifade etmenin bir yoludur. Aşağıdaki CREATE TABLE ifadesinde, FOREIGN KEY kısıtlaması, ekip tablosundaki productSpecialty sütununa eklenen herhangi bir değerin Products tablosunun productID sütununda da bulunmasını gerektirir.

Aşağıdaki üç sütundan oluşan Takım adında bir tablo oluşturun:

CREATE TABLE team (
empID int UNIQUE,
empName varchar(20),
productSpecialty int,
PRIMARY KEY (empID),
FOREIGN KEY (productSpecialty) REFERENCES products (productID)
);

Oluşturacağınız son tablo, şirketin satış kayıtlarını içerecektir. Bu tabloda dört sütun bulunur:

• saleID: productID ve empID sütunlarına benzer şekilde, bu sütun her satış için int veri türü olarak ifade edilen benzersiz bir kimlik numarası içerir. Bu sütun ayrıca satış tablosunun birincil anahtarı olarak işlev görebilmesi için bir UNIQUE kısıtlamasına sahiptir.
• Miktar: Satılan her ürünün adet sayısı int veri tipiyle ifade edilir.
• Ürün Kimliği: Satılan ürünün tam sayı olarak ifade edilen kimlik numarası.
• Satış Elemanı: Satışı gerçekleştiren çalışanın kimlik numarası.

Takım tablosundaki productSpecialty sütununda olduğu gibi, hem productID hem de salesperson sütunlarına FOREIGN KEY kısıtlamaları uygulamaya karar verirsiniz. Bu, bu sütunların yalnızca products tablosunun productID sütununda ve team tablosunun empID sütunlarında bulunan değerleri içermesini sağlar.

Aşağıdaki dört sütundan oluşan Satışlar adında bir tablo oluşturun:

CREATE TABLE sales (
saleID int UNIQUE,
quantity int,
productID int,
salesperson int,
PRIMARY KEY (saleID),
FOREIGN KEY (productID) REFERENCES products (productID),
FOREIGN KEY (salesperson) REFERENCES team (empID)
);

Daha sonra aşağıdaki INSERT INTO işlemini yürüterek Products tablosunu örnek verilerle yükleyin:

INSERT INTO products
VALUES
(1, 'widget', 18.99),
(2, 'gizmo', 14.49),
(3, 'thingamajig', 39.99),
(4, 'doodad', 11.50),
(5, 'whatzit', 29.99);

Daha sonra takım tablosunu örnek verilerle yükleyin:

INSERT INTO team
VALUES
(1, 'Florence', 1),
(2, 'Mary', 4),
(3, 'Diana', 3),
(4, 'Betty', 2);

Satış tablosunu örnek verilerle de yükleyin:

INSERT INTO sales
VALUES
(1, 7, 1, 1),
(2, 10, 5, 4),
(3, 8, 2, 4),
(4, 1, 3, 3),
(5, 5, 1, 3);

Son olarak, şirketinizin satış ekibinizden hiç kimsenin katılımı olmadan birkaç satış yaptığını düşünün. Bu satışları kaydetmek için, Satış Elemanı sütununda değeri olmayan üç satırı Satış tablosuna eklemek üzere aşağıdaki işlemi çalıştırın:

INSERT INTO sales (saleID, quantity, productID)
VALUES
(6, 1, 5),
(7, 3, 1),
(8, 4, 5);

Bunu yaptıktan sonra, kılavuzun geri kalanını takip etmeye ve SQL'de tabloları nasıl birleştireceğinizi öğrenmeye hazırsınız.

JOIN işleminin sözdizimini anlama

JOIN ifadeleri, UPDATE ve DELETE işlemleri de dahil olmak üzere çeşitli SQL ifadelerinde kullanılabilir. Ancak, bu kılavuzdaki örnekler, açıklayıcı olması açısından, JOIN ifadelerinin nasıl çalıştığını göstermek için SELECT sorgularını kullanır.

Aşağıdaki örnek, JOIN ifadesini içeren bir SELECT ifadesinin genel sözdizimini göstermektedir:

SELECT table1.column1, table2.column2
FROM table1 JOIN table2
ON search_condition;

Bu sözdizimi, iki ayrı tablodan iki sütun döndüren bir SELECT ifadesiyle başlar. JOIN ifadeleri birden fazla tablonun içeriğini karşılaştırdığından, bu örnek sözdizimi her sütunun hangi tablodan seçileceğini belirtir ve sütun adından önce tablo adını ve bir nokta işareti koyar. Bu, tam nitelikli sütun referansı olarak bilinir.

Bunlar gibi tam nitelikli sütun referanslarını herhangi bir işlemde kullanabilirsiniz, ancak teknik olarak bu yalnızca farklı tablolardaki iki sütunun aynı ada sahip olduğu işlemlerde gereklidir. Ancak, birden fazla tabloyla çalışırken bunları kullanmak iyi bir uygulamadır, çünkü JOIN işlemlerinin okunabilirliğini ve anlaşılmasını kolaylaştırabilirler.

SELECT ifadesinden sonra FROM ifadesi gelir. Herhangi bir sorguda, FROM ifadesi, istediğiniz verileri döndürmek için aranması gereken veri kümesini tanımladığınız yerdir. Buradaki tek fark, FROM ifadesinin JOIN anahtar sözcüğüyle ayrılmış iki tablodan oluşmasıdır. Sorgu yazmayı düşünmenin faydalı bir yolu, sorgulamak istediğiniz tablodan döndürülecek sütunları seçmeyi unutmamaktır.

Ardından, sorgunun iki tabloyu bir arama koşulu tanımlayarak nasıl birleştirmesi gerektiğini açıklayan bir ON ifadesi bulunur. Bir arama koşulu, belirli bir koşul için "doğru", "yanlış" veya "bilinmiyor" olarak değerlendirilebilen bir veya daha fazla ifade veya ifadeden oluşan bir kümedir. JOIN işlemini, her iki tablodaki tüm satırları birleştirmek ve ardından ON ifadesindeki arama koşulunun "doğru" olarak değerlendirildiği tüm satırları döndürmek olarak düşünmek faydalı olabilir.

Bir ON ifadesine, genellikle iki ilişkili sütunun (örneğin, bir tablonun yabancı anahtarı ve yabancı anahtarın referans verdiği başka bir tablonun birincil anahtarı) eşit değerlere sahip olup olmadığını test eden bir arama koşulu eklemek mantıklıdır. Bu bazen eşit birleştirme olarak adlandırılır.

Equi'nin birden fazla tablodan eşleşen verileri nasıl birleştirebileceğine dair bir örnek olarak, daha önce eklediğiniz örnek verileri kullanarak aşağıdaki sorguyu çalıştırın. Bu sorgu, Ürünler ve Ekip tablolarını, ilgili productID ve productSpecialty sütunlarındaki eşleşen değerleri test eden bir arama koşuluyla birleştirir. Ardından, her satış ekibi üyesinin adını, uzmanlaştığı her ürünün adını ve bu ürünlerin fiyatını döndürür:

SELECT team.empName, products.productName, products.price
FROM products JOIN team
ON products.productID = team.productSpecialty

Output
+----------+-------------+-------+
| empName | productName | price |
+----------+-------------+-------+
| Florence | widget | 18.99 |
| Mary | doodad | 11.50 |
| Diana | thingamajig | 39.99 |
| Betty | gizmo | 14.49 |
+----------+-------------+-------+
4 rows in set (0.00 sec)

JOIN Process Example
+-----------+-------------+-------+
| productID | productName | price |
+-----------+-------------+-------+
| 1 | widget | 18.99 |
| 2 | gizmo | 14.49 |
| 3 | thingamajig | 39.99 |
| 4 | doodad | 11.50 |
| 5 | whatzit | 29.99 |
+-----------+-------------+-------+

JOIN Process Example
+-----------+-------------+-------+-------+----------+------------------+
| productID | productName | price | empID | empName | productSpecialty |
+-----------+-------------+-------+-------+----------+------------------+
| 1 | widget | 18.99 | 1 | Florence | 1 |
| 2 | gizmo | 14.49 | 4 | Betty | 2 |
| 3 | thingamajig | 39.99 | 3 | Diana | 3 |
| 4 | doodad | 11.50 | 2 | Mary | 4 |
| 5 | whatzit | 29.99 | | | |
+-----------+-------------+-------+-------+----------+------------------+

JOIN Process Example
+----------+-------------+-------+
| empName | productName | price |
+----------+-------------+-------+
| Florence | widget | 18.99 |
| Mary | doodad | 11.50 |
| Diana | thingamajig | 39.99 |
| Betty | gizmo | 14.49 |
+----------+-------------+-------+
4 rows in set (0.00 sec)

SELECT table1.column1, table2.column2
FROM table1 JOIN table2
USING (related_column);

SELECT sales.saleID, sales.quantity, products.productName, products.price
FROM sales JOIN products
USING (productID)
ORDER BY saleID;

Output
+--------+----------+-------------+-------+
| saleID | quantity | productName | price |
+--------+----------+-------------+-------+
| 1 | 7 | widget | 18.99 |
| 2 | 10 | whatzit | 29.99 |
| 3 | 8 | gizmo | 14.49 |
| 4 | 1 | thingamajig | 39.99 |
| 5 | 5 | widget | 18.99 |
| 6 | 1 | whatzit | 29.99 |
| 7 | 3 | widget | 18.99 |
| 8 | 4 | whatzit | 29.99 |
+--------+----------+-------------+-------+
8 rows in set (0.00 sec)

SELECT table1.column1, table2.column2, table3.column3
FROM table1 JOIN table2
ON table1.related_column = table2.related_column
JOIN table3
ON table3.related_column = table1_or_2.related_column;

SELECT sales.saleID,
team.empName,
products.productName,
(sales.quantity * products.price)
FROM products JOIN sales
USING (productID)
JOIN team
ON team.productSpecialty = sales.productID
WHERE team.empID = sales.salesperson
ORDER BY sales.saleID;

Output
+--------+----------+-------------+-----------------------------------+
| saleID | empName | productName | (sales.quantity * products.price) |
+--------+----------+-------------+-----------------------------------+
| 1 | Florence | widget | 132.93 |
| 3 | Betty | gizmo | 115.92 |
| 4 | Diana | thingamajig | 39.99 |
+--------+----------+-------------+-----------------------------------+
3 rows in set (0.00 sec)

SELECT sales.saleID, sales.quantity, sales.salesperson, team.empName 
FROM sales JOIN team
ON sales.salesperson = team.empID;

Output
+--------+----------+-------------+----------+
| saleID | quantity | salesperson | empName |
+--------+----------+-------------+----------+
| 1 | 7 | 1 | Florence |
| 4 | 1 | 3 | Diana |
| 5 | 5 | 3 | Diana |
| 2 | 10 | 4 | Betty |
| 3 | 8 | 4 | Betty |
+--------+----------+-------------+----------+
5 rows in set (0.00 sec)

SELECT sales.saleID, sales.quantity, sales.salesperson, team.empName
FROM sales LEFT OUTER JOIN team
ON sales.salesperson = team.empID;

Output
+--------+----------+-------------+----------+
| saleID | quantity | salesperson | empName |
+--------+----------+-------------+----------+
| 1 | 7 | 1 | Florence |
| 2 | 10 | 4 | Betty |
| 3 | 8 | 4 | Betty |
| 4 | 1 | 3 | Diana |
| 5 | 5 | 3 | Diana |
| 6 | 1 | NULL | NULL |
| 7 | 3 | NULL | NULL |
| 8 | 4 | NULL | NULL |
+--------+----------+-------------+----------+
8 rows in set (0.00 sec)

SELECT sales.saleID, sales.quantity, sales.salesperson, team.empName
FROM sales RIGHT JOIN team
ON sales.salesperson = team.empID;

Output
+--------+----------+-------------+----------+
| saleID | quantity | salesperson | empName |
+--------+----------+-------------+----------+
| 1 | 7 | 1 | Florence |
| NULL | NULL | NULL | Mary |
| 4 | 1 | 3 | Diana |
| 5 | 5 | 3 | Diana |
| 2 | 10 | 4 | Betty |
| 3 | 8 | 4 | Betty |
+--------+----------+-------------+----------+
6 rows in set (0.00 sec)

SELECT t1.column1, t2.column2
FROM table1 AS t1 JOIN table2 AS t2
ON t1.related_column = t2.related_column;

SELECT S.saleID, S.quantity,
P.productName,
(P.price * S.quantity) AS revenue 
FROM sales AS S JOIN products AS P
USING (productID);

Output
+--------+----------+-------------+---------+
| saleID | quantity | productName | revenue |
+--------+----------+-------------+---------+
| 1 | 7 | widget | 132.93 |
| 2 | 10 | whatzit | 299.90 |
| 3 | 8 | gizmo | 115.92 |
| 4 | 1 | thingamajig | 39.99 |
| 5 | 5 | widget | 94.95 |
| 6 | 1 | whatzit | 29.99 |
| 7 | 3 | widget | 56.97 |
| 8 | 4 | whatzit | 119.96 |
+--------+----------+-------------+---------+
8 rows in set (0.00 sec)

SELECT S.saleID, S.quantity, P.productName, (P.price * S.quantity) revenue 
FROM sales S JOIN products P
USING (productID);