كيفية استخدام Joins في SQL

مقدمة

تُقسّم العديد من تصميمات قواعد البيانات المعلومات إلى جداول مختلفة بناءً على العلاقات بين نقاط بيانات مُحددة. حتى في مثل هذه الحالات، قد يرغب المستخدمون أحيانًا في استرجاع معلومات من أكثر من جدول في آنٍ واحد.

من الطرق الشائعة للوصول إلى البيانات من جداول متعددة في عمليات لغة الاستعلام الهيكلية (SQL) دمج الجداول باستخدام عبارة JOIN. استنادًا إلى عملية الدمج في الجبر العلائقي، تجمع عبارة JOIN جداول منفصلة عن طريق مطابقة الصفوف المرتبطة في كل جدول. عادةً، تعتمد هذه العلاقة على زوج من الأعمدة - عمود واحد من كل جدول - يتشاركان قيمًا، مثل المفتاح الخارجي لأحد الجدولين والمفتاح الأساسي لجدول آخر يشير إليه المفتاح الخارجي.

يشرح هذا الدليل كيفية بناء استعلامات SQL متنوعة تتضمن جملة JOIN. كما يوضح أنواع جمل JOIN المختلفة، وكيفية دمج البيانات من جداول متعددة، وكيفية استخدام أسماء مستعارة للأعمدة لتسهيل كتابة عمليات JOIN.

المتطلبات الأساسية

لمتابعة هذا الدليل، ستحتاج إلى جهاز كمبيوتر مُجهّز بنظام إدارة قواعد بيانات علائقية (RDBMS) يستخدم لغة SQL. تم التحقق من التعليمات والأمثلة الواردة في هذا الدليل باستخدام البيئة التالية:

• خادم يعمل بنظام التشغيل Ubuntu 20.04، مع مستخدم غير جذر يتمتع بامتيازات إدارية وجدار حماية مُهيأ باستخدام UFW
• تم تثبيت MySQL وتأمينه على الخادم.
• ستحتاج أيضًا إلى قاعدة بيانات تحتوي على جداول مُحمّلة ببيانات نموذجية يمكنك استخدامها للتدرب على استخدام عمليات JOIN. ننصحك بمتابعة قسم "الاتصال بـ MySQL وإعداد قاعدة بيانات نموذجية" أدناه للحصول على تفاصيل حول كيفية الاتصال بخادم MySQL وإنشاء قاعدة البيانات التجريبية المستخدمة في الأمثلة الواردة في هذا الدليل.

الاتصال بـ MySQL وإعداد قاعدة بيانات نموذجية

إذا كان نظام قاعدة بيانات SQL الخاص بك يعمل على خادم بعيد، فقم بتسجيل الدخول إلى الخادم الخاص بك عبر SSH من جهازك المحلي:

ssh sammy@your_server_ip

ثم افتح موجه خادم MySQL واستبدل Sami باسم المستخدم الخاص بك في 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. يعمل هذا العمود كمفتاح أساسي للجدول، أي أن كل قيمة تُعَدُّ مُعرِّفًا فريدًا للصف المُقابل. ونظرًا لأن كل قيمة في المفتاح الأساسي يجب أن تكون فريدة، يُطبَّق على هذا العمود قيد "فريد".
• productName: يتم التعبير عن اسم كل منتج باستخدام نوع البيانات varchar بحد أقصى 20 حرفًا.
• السعر: سعر كل منتج، مُعبَّر عنه باستخدام نظام البيانات العشري. هذا يعني أن كل قيمة في هذا العمود محدودة بأربعة أرقام كحد أقصى، بحيث يكون رقمان منها على يمين الفاصلة العشرية. وبالتالي، يتراوح نطاق القيم المسموح بها في هذا العمود بين -99.99 و99.99.

قم بإنشاء جدول باسم المنتجات يحتوي على الأعمدة الثلاثة التالية:

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

الجدول الثاني يخزّن معلومات حول موظفي فريق المبيعات في شركتك. قررتَ أن هذا الجدول يحتاج أيضًا إلى ثلاثة أعمدة:

• معرف الموظف: على غرار عمود معرف المنتج، يحتوي هذا العمود على رقم تعريف فريد لكل موظف في فريق المبيعات، مُعبَّر عنه بنوع بيانات int. وبالتالي، سيكون لهذا العمود قيد فريد، وسيُستخدم كمفتاح أساسي لجدول الفريق.
• empName: اسم كل مندوب مبيعات يتم التعبير عنه باستخدام نوع البيانات varchar بحد أقصى 20 حرفًا.
• تخصص المنتج: يُخصص لكل عضو في فريق المبيعات منتجًا كتخصص. يمكنهم بيع أي منتج تُنتجه شركتك، ولكن تركيزهم منصبّ بشكل عام على المنتج الذي يتخصصون فيه. لتمثيل ذلك في الجدول، أنشئ هذا العمود الذي يحتوي على قيمة مُعرّف المنتج لكل منتج يتخصص فيه كل موظف.

لضمان احتواء عمود productSpecialty فقط على قيم تُمثل أرقام مُعرّفات منتجات صالحة، عليك تطبيق قيد مفتاح خارجي على العمود الذي يُشير إلى عمود productID في جدول "المنتجات". يُعد قيد المفتاح الخارجي طريقةً للتعبير عن علاقة بين جدولين من خلال اشتراط وجود قيم العمود الذي يُطبّق عليه في العمود الذي يُشير إليه. في عبارة CREATE TABLE التالية، يشترط قيد المفتاح الخارجي وجود أي قيمة مُضافة إلى عمود productSpecialty في جدول الفريق في عمود productID في جدول "المنتجات".

قم بإنشاء جدول يسمى الفريق مع هذه الأعمدة الثلاثة:

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

سيحتوي الجدول الأخير الذي تُنشئه على سجلات مبيعات الشركة. سيحتوي هذا الجدول على أربعة أعمدة:

• معرف البيع: على غرار عمودي معرف المنتج ومعرف الموظف، يحتوي هذا العمود على رقم تعريف فريد لكل عملية بيع، يُعبَّر عنه بنوع بيانات int. يحتوي هذا العمود أيضًا على قيد فريد، ليكون بمثابة المفتاح الأساسي لجدول المبيعات.
• الكمية: يتم التعبير عن عدد وحدات كل منتج مباع باستخدام نوع البيانات 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، بما في ذلك عمليتي التحديث والحذف. ولأغراض التوضيح، تستخدم الأمثلة في هذا الدليل استعلامات 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، شغّل الاستعلام التالي باستخدام بيانات العينة التي أضفتها سابقًا. يربط هذا الاستعلام جدولي المنتجات والفريق باستخدام شرط بحث يختبر القيم المتطابقة في عمودي "معرّف المنتج" و"تخصص المنتج" الخاصين بهما. ثم يُرجع اسم كل عضو في فريق المبيعات، واسم كل منتج يتخصص فيه، وسعره.

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