نحوه استفاده از Joins در SQL

مقدمه

بسیاری از طراحی های پایگاه داده بر اساس روابط بین نقاط داده خاص، اطلاعات را به جداول مختلف جدا می کنند. حتی در مواردی مانند این، این احتمال وجود دارد که زمان هایی وجود داشته باشد که شخصی بخواهد اطلاعات را از بیش از یک جدول در یک زمان بازیابی کند.

یک راه متداول برای دسترسی به داده ها از چندین جدول در یک عملیات زبان پرس و جو ساختاریافته (SQL) ترکیب جداول با یک بند JOIN است. بر اساس عملیات پیوستن در جبر رابطه‌ای، یک بند JOIN جداول جداگانه را با تطبیق ردیف‌هایی در هر جدول که به یکدیگر مرتبط هستند، ترکیب می‌کند. معمولاً، این رابطه مبتنی بر یک جفت ستون – یکی از هر جدول – است که مقادیر مشترکی دارند، مانند کلید خارجی یک جدول و کلید اصلی جدول دیگری که کلید خارجی به آن ارجاع می‌دهد.

این راهنما نحوه ساخت انواع پرس‌و‌جوهای SQL که شامل یک بند JOIN است را تشریح می‌کند. همچنین انواع مختلف بندهای JOIN، نحوه ترکیب داده‌ها از چندین جداول، و نحوه نام مستعار ستون‌ها را نشان می‌دهد تا نوشتن عملیات JOIN کمتر خسته‌کننده باشد.

پیش نیازها

برای پیروی از این راهنما، به رایانه ای نیاز دارید که دارای نوعی سیستم مدیریت پایگاه داده رابطه ای (RDBMS) است که از SQL استفاده می کند. دستورالعمل ها و مثال های موجود در این راهنما با استفاده از محیط زیر تأیید شدند:

• سروری که اوبونتو 20.04 را اجرا می کند، با یک کاربر غیر ریشه با امتیازات مدیریتی و یک فایروال پیکربندی شده با UFW
• MySQL روی سرور نصب و ایمن شده است
• شما همچنین به یک پایگاه داده با تعدادی جداول بارگذاری شده با داده های نمونه نیاز دارید که می توانید از آنها برای تمرین با استفاده از عملیات JOIN استفاده کنید. ما شما را تشویق می کنیم تا برای جزئیات نحوه اتصال به سرور MySQL و ایجاد پایگاه داده آزمایشی مورد استفاده در نمونه های این راهنما، بخش اتصال به MySQL و راه اندازی یک پایگاه داده نمونه زیر را طی کنید.

اتصال به MySQL و راه اندازی یک پایگاه داده نمونه

اگر سیستم پایگاه داده SQL شما بر روی یک سرور راه دور اجرا می شود، SSH را از دستگاه محلی خود به سرور خود وارد کنید:

ssh sammy@your_server_ip

سپس اعلان سرور MySQL را باز کنید و نام حساب کاربری MySQL خود را جایگزین سامی کنید:

mysql -u sammy -p

یک پایگاه داده به نام joinsDB ایجاد کنید:

CREATE DATABASE joinsDB;

اگر پایگاه داده با موفقیت ایجاد شود، خروجی زیر را دریافت خواهید کرد:

Output
Query OK, 1 row affected (0.01 sec)

برای انتخاب پایگاه داده joinsDB، عبارت USE زیر را اجرا کنید:

USE joinsDB;

Output
Database changed

پس از انتخاب joinsDB، چند جدول درون آن ایجاد کنید. برای مثال‌های مورد استفاده در این راهنما، تصور کنید که کارخانه‌ای را اداره می‌کنید و تصمیم گرفته‌اید اطلاعات مربوط به خط تولید، کارکنان تیم فروش و فروش شرکت‌تان را در پایگاه داده SQL ردیابی کنید. شما قصد دارید با سه جدول شروع کنید که در جدول اول اطلاعات مربوط به محصولات شما ذخیره می شود. شما تصمیم می گیرید که این جدول اول به سه ستون نیاز دارد:

• شناسه محصول: شماره شناسایی هر محصول که با نوع داده int بیان می شود. این ستون به عنوان کلید اصلی جدول عمل می کند، به این معنی که هر مقدار به عنوان یک شناسه منحصر به فرد برای ردیف مربوطه عمل می کند. از آنجا که هر مقدار در یک کلید اصلی باید منحصر به فرد باشد، این ستون همچنین دارای یک محدودیت UNIQUE برای آن اعمال می شود
• productName: نام هر محصول با استفاده از نوع داده varchar با حداکثر 20 کاراکتر بیان شده است.
• قیمت: قیمت هر محصول که با استفاده از نوع داده اعشاری بیان می شود. این عبارت مشخص می کند که هر مقدار در این ستون حداکثر به طول چهار رقم محدود می شود و دو عدد از آن رقم در سمت راست نقطه اعشار قرار دارند. بنابراین، محدوده مقادیر مجاز در این ستون از 99.99- تا 99.99 می رود

جدولی با نام محصولات ایجاد کنید که دارای این سه ستون باشد:

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

جدول دوم اطلاعات مربوط به کارکنان تیم فروش شرکت شما را ذخیره می کند. شما تصمیم می گیرید که این جدول نیز به سه ستون نیاز دارد:

• empID: مشابه ستون productID، این ستون یک شماره شناسایی منحصر به فرد برای هر کارمند در تیم فروش دارد که با نوع داده int بیان شده است. به همین ترتیب، این ستون دارای یک محدودیت UNIQUE خواهد بود و به عنوان کلید اصلی جدول تیم عمل می کند.
• empName: نام هر فروشنده که با استفاده از نوع داده varchar با حداکثر 20 کاراکتر بیان می شود.
• productSpecialty: به هر یک از اعضای تیم فروش شما یک محصول به عنوان تخصص اختصاص داده شده است. آنها می توانند هر محصولی را که شرکت شما تولید می کند بفروشند، اما تمرکز کلی آنها بر روی هر محصولی است که در آن تخصص دارند. برای نشان دادن این موضوع در جدول، این ستون را ایجاد می کنید که مقدار productID هر محصولی را که هر کارمند در آن تخصص دارد را نگه می دارد.

برای اطمینان از اینکه ستون productSpecialty فقط دارای مقادیری است که شماره شناسه معتبر محصول را نشان می‌دهد، تصمیم می‌گیرید یک محدودیت کلید خارجی برای ستونی اعمال کنید که به ستون productID جدول محصولات اشاره دارد. یک محدودیت کلید خارجی روشی برای بیان رابطه بین دو جدول با الزام این است که مقادیر در ستونی که روی آن اعمال می شود باید در ستونی که به آن ارجاع می دهد وجود داشته باشد. در عبارت CREATE TABLE زیر، محدودیت FOREIGN KEY مستلزم آن است که هر مقداری که به ستون productSpecialty در جدول team اضافه می‌شود باید در ستون productID جدول محصولات وجود داشته باشد.

با این سه ستون جدولی به نام تیم ایجاد کنید:

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

آخرین جدولی که ایجاد می کنید، رکوردهای فروش شرکت را نگه می دارد. این جدول دارای چهار ستون خواهد بود:

• saleID: شبیه به productID و empIDcolumns، این ستون دارای یک شماره شناسایی منحصر به فرد برای هر فروش است که با نوع داده int بیان شده است. این ستون همچنین دارای یک محدودیت UNIQUE است تا بتواند به عنوان کلید اصلی جدول فروش عمل کند
• مقدار: تعداد واحدهای هر محصول فروخته شده با نوع داده int بیان می شود
• شناسه محصول: شماره شناسایی محصول فروخته شده به صورت int بیان می شود
• فروشنده: شماره شناسایی کارمندی که فروش را انجام داده است

مانند ستون productSpecialty از جدول تیم، شما تصمیم می‌گیرید که محدودیت‌های FOREIGN KEY را برای هر دو ستون productID و salesperson اعمال کنید. این اطمینان حاصل می کند که این ستون ها فقط حاوی مقادیری هستند که به ترتیب در ستون productID جدول محصولات و ستون های empID جدول تیم وجود دارند.

با این چهار ستون جدولی به نام فروش ایجاد کنید:

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)
);

پس از آن، با اجرای عملیات INSERT INTO زیر، جدول محصولات را با داده های نمونه بارگیری کنید:

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

سپس جدول تیم را با داده های نمونه بارگیری کنید:

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

جدول فروش را با داده های نمونه نیز بارگیری کنید:

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

در نهایت، تصور کنید که شرکت شما بدون دخالت کسی در تیم فروش شما، چند فروش انجام می دهد. برای ثبت این فروش‌ها، عملیات زیر را اجرا کنید تا سه ردیف به جدول فروش اضافه کنید که مقداری برای ستون فروشنده وجود ندارد:

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

با این کار، شما آماده هستید تا بقیه راهنما را دنبال کنید و شروع به یادگیری نحوه اتصال جداول به یکدیگر در SQL کنید.

درک نحو عملیات JOIN

بندهای JOIN را می توان در انواع دستورات SQL از جمله عملیات UPDATE و DELETE استفاده کرد. با این حال، برای اهداف تصویری، مثال‌های موجود در این راهنما از پرس‌وجوهای SELECT استفاده می‌کنند تا نحوه عملکرد بندهای JOIN را نشان دهند.

مثال زیر نحو کلی یک عبارت SELECT را نشان می دهد که شامل یک عبارت JOIN است:

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

این نحو با یک عبارت SELECT شروع می شود که دو ستون از دو جدول جداگانه را برمی گرداند. توجه داشته باشید که از آنجایی که بندهای JOIN محتویات بیش از یک جدول را با هم مقایسه می‌کنند، این نحو مثالی مشخص می‌کند که هر ستون از کدام جدول انتخاب شود، قبل از نام ستون با نام جدول و نقطه. این به عنوان یک مرجع ستون کاملاً واجد شرایط شناخته می شود.

شما می توانید از ارجاعات ستون کاملاً واجد شرایط مانند اینها در هر عملیاتی استفاده کنید، اما انجام این کار از نظر فنی فقط در عملیاتی ضروری است که دو ستون از جداول مختلف یک نام دارند. با این حال، استفاده از آنها هنگام کار با چندین جدول، تمرین خوبی است، زیرا می توانند به خواندن و درک عملیات JOIN کمک کنند.

بعد از SELECT عبارت FROM می آید. در هر پرس و جو، عبارت FROM جایی است که شما مجموعه داده هایی را که باید جستجو شوند تا داده های مورد نظر را بازگردانید، تعریف می کنید. تنها تفاوت در اینجا این است که عبارت FROM شامل دو جدول است که با کلمه کلیدی JOIN از هم جدا شده اند. یک راه مفید برای فکر کردن به نوشتن پرس‌و‌جوها این است که به خاطر داشته باشید که ستون‌هایی را انتخاب کنید که از کدام جدولی که می‌خواهید پرس‌وجو کنید، بازگردانده شوند.

پس از آن یک عبارت ON وجود دارد که توضیح می دهد چگونه پرس و جو باید دو جدول را با تعریف یک شرط جستجو به هم بپیوندد. شرط جستجو مجموعه‌ای از یک یا چند گزاره یا عبارت است که می‌تواند «درست»، «نادرست» یا «ناشناخته» بودن یک شرط خاص را ارزیابی کند. این می تواند مفید باشد که عملیات JOIN را به عنوان ترکیب هر سطر از هر دو جدول و سپس برگرداندن هر ردیفی که شرط جستجو در عبارت ON برای آنها “درست” ارزیابی می شود، مفید باشد.

در یک عبارت ON، معمولاً منطقی است که یک شرط جستجو را شامل شود که آزمایش کند آیا دو ستون مرتبط – مانند کلید خارجی یک جدول و کلید اصلی جدول دیگری که کلید خارجی به آن ارجاع می دهد – دارای مقادیر برابر هستند یا خیر. این گاهی اوقات به عنوان یک equi join نامیده می شود.

به عنوان مثالی از نحوه پیوستن equi به داده های منطبق از چندین جدول، پرس و جو زیر را با استفاده از داده های نمونه ای که قبلا اضافه کرده اید اجرا کنید. این عبارت به جداول محصولات و تیم با یک شرط جستجو می پیوندد که مقادیر تطبیق را در ستون های productID و productSpecialty مربوطه آنها آزمایش می کند. سپس نام هر یک از اعضای تیم فروش، نام هر محصولی که در آن تخصص دارند و قیمت آن محصولات را برمی گرداند:

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);