غالبًا ما تفصل تصاميم قواعد البيانات المعلومات إلى جداول مختلفة بناءً على العلاقات بين بعض نقاط البيانات. ولكن حتى في مثل هذه الحالات، من المحتمل أن نرغب أحيانًا باسترجاع المعلومات من أكثر من جدول في وقتٍ واحد.
إحدى الطرق الشائعة للوصول إلى البيانات من جداول متعددة في عملية واحدة باستخدام لغة الاستعلام البنيوية SQL هي تجميع الجداول باستخدام بنى الدمج JOIN. إذ تُجمّع بنية الدمج JOIN الجداول المنفصلة عن طريق مطابقة السجلات المرتبطة ببعضها البعض من كل جدول مستندةً إلى عمليات الدمج في الجبر العلاقيّ -وهو نظرية تستخدم الهياكل الجبرية لنمذجة البيانات وتحديد الاستعلامات عليها، إذ يُعد إطارًا نظريًا يُستخدم لوصف العمليات على البيانات في قواعد البيانات العلاقية، مثل الدمج والاختيار، ويساعد في تشكيل الأساس الرياضي للغات الاستعلام مثل SQL) -. وعادةً ما تُبنى العلاقة بين الجداول المطلوب دمجها على زوجٍ من الأعمدة - عمود من كل جدول - والتي تتشارك قيمًا مشتركة، كأن نختار مفتاح خارجي لجدول مع مفتاح أساسي لجدول آخر يُشير إليه المفتاح الخارجي آنف الذكر.
يوضّح هذا المقال كيفية بناء مجموعة متنوعة من استعلامات SQL التي تتضمن بنية الدمج JOIN. كما يُسلط الضوء على أنماط مختلفة من بنى الدمج وكيفية تجميع البيانات من جداول متعددة وكيفية استخدام الأسماء البديلة alias للأعمدة لجعل كتابة عمليات الدمج JOIN أقل تعقيدًا.
مستلزمات العمل
لمتابعة الخطوات في هذا المقال، ستحتاج إلى جهاز كمبيوتر يُشغّل أحد أنواع أنظمة إدارة قواعد البيانات العلاقية RDBMS التي تستخدم SQL. وقد اختبرنا الأوامر البرمجية والأمثلة في هذا المقال مستخدمين البيئة التالية:
- خادم عامل على توزيعة أوبنتو، مع مستخدم ذو صلاحيات مسؤول مختلف عن المستخدم الجذر، وجدار حماية مكوّن باستخدام UFW، كما هو موضح في دليل الإعداد الأولي للخادم مع الإصدار 20.04 من أوبنتو، كما يمكنك الاطلاع على المقال كيفية تثبيت توزيعة أوبنتو من لينكس بأبسط طريقة.
- MySQL مثبتة ومؤمنة على الخادم، كما هو موضح في المقال كيفية تثبيت MySQL على أوبونتو. وقد نفذنا خطوات هذا المقال باستخدام مستخدم MySQL مختلف عن المستخدم الجذر، مُنشأ وفق الطريقة الموضحة في الخطوة 3 من هذا المقال.
ملاحظة: تجدر الإشارة إلى أنّ الكثير من أنظمة إدارة قواعد البيانات العلاقية لها تقديماتها الفريدة من لغة SQL. فبالرغم من كون الأوامر المُقدمة في هذا المقال ستعمل مع معظم هذه الأنظمة، ولكن قد تجد بعض الاختلافات في الصيغة أو الناتج عند تنفيذها على أنظمة مختلفة عن MySQL.
وبالعودة إلى مستلزمات العمل، ستحتاج أيضًا إلى قاعدة بيانات وجدول مُحمّل ببعض البيانات التجريبية النموذجية لتتمكن من التدرب على استخدام عمليات الدمج JOIN. لذا ننصحك بمتابعة القسم القادم الاتصال بـ MySQL وإعداد قاعدة بيانات تجريبية نموذجية للمزيد من التفاصيل حول كيفية إعداد قاعدة بيانات وجدول لاستخدامهما في الأمثلة خلال هذا المقال.
الاتصال بـ MySQL وإعداد قاعدة بيانات تجريبية نموذجية
إذا كان نظام قاعدة بيانات SQL الخاص بك يعمل على خادم عن بُعد، اتصل بالخادم مُستخدمًا بروتوكول SSH من جهازك المحلي على النحو التالي:
$ ssh ssh user@your_server_ip
ثم افتح واجهة سطر الأوامر في خادم MySQL، مُستبدلًا user باسم حساب مستخدم MySQL الخاص بك:
$ mysql -u user -p
أنشئ قاعدة بيانات باسمjoinsDB:
mysql> CREATE DATABASE joinsDB;
وبمجرّد إنشاء قاعدة البيانات بنجاح ستحصل على خرج كالتالي:
الخرج Query OK, 1 row affected (0.01 sec)
ولاختيار قاعدة البيانات joinsDB، نفّذ تعليمة USE التالية:
mysql> USE joinsDB;
الخرج Database changed
الآن، بعد اختيار قاعدة البيانات joinsDB لننشئ بعض الجداول ضمنها.
لمتابعة الأمثلة المستخدمة في هذا المقال، تخيل أنك تدير مصنعًا وقررت البدء في تتبع المعلومات حول كل من خط الإنتاج، والموظفين في فريق المبيعات، ومبيعات الشركة، وذلك في قاعدة بيانات SQL. فقررت البدء بثلاثة جداول، أولها سيخزن معلومات حول المنتجات، متضمنًا ثلاثة أعمدة:
-
productID: رقم تعريف كل منتج، معبرًا عنه بنمط البياناتint. سيعمل هذا العمود كمفتاح أساسي للجدول، مما يعني أن كل قيمة فيه ستلعب دور مُعرّف فريد للسجل الخاص بها. وبما أن كل قيمة في المفتاح الأساسي يجب أن تكون فريدة، ستطبّق على هذا العمود القيدUNIQUE. -
productName: اسم كل منتج، معبرًا عنه باستخدام نمط البياناتvarcharبحد أقصى 20 محرفًا. -
price: سعر كل منتج، معبرًا عنه باستخدام نمط البياناتdecimal. وتُحدد القيم في هذا العمود بحد أقصى قدره أربعة أرقام، بواقع رقمين على يمين الفاصلة العشرية. وبالتالي، تتراوح القيم المسموح بها من-99.99إلى99.99.
إذًا، أنشئ جدولًا باسم products يحتوي على هذه الأعمدة الثلاثة على النحو التالي:
mysql> CREATE TABLE products ( mysql> productID int UNIQUE, mysql> productName varchar(20), mysql> price decimal (4,2), mysql> PRIMARY KEY (productID) mysql>);
في حين سيخزّن الجدول الثاني معلومات حول الموظفين في فريق مبيعات الشركة. فارتأيت أن هذا الجدول يحتاج أيضًا إلى ثلاثة أعمدة:
-
empID: مشابه لعمودproductID، إذ سيحتوي على مُعرّف فريد لكل موظف في فريق المبيعات مُعبرًا عنه بنمط البياناتint. وبالمثل، سيُطبق على هذا العمود قيدUNIQUEوسيعمل كمفتاح أساسي لجدول الفريق. -
empName: اسم كل مندوب مبيعات، مُعبرًا عنه باستخدام نمط البياناتvarcharبحد أقصى 20 محرفًا. -
productSpecialty: بفرض أنك قررت تخصيص منتج لكل عضو في فريق المبيعات؛ إذ يمكنه بيع أي منتج تصنعه الشركة ولكن تركيزه العام سيكون على المنتج المُخصّص له. وللإشارة إلى هذا الأمر في الجدول، أنشأنا هذا العمود الذي يحتوي على قيمةproductIDللمنتج المُخصّص لكل موظف.
ولضمان أنّ عمود productSpecialty لن يتضمّن سوى قيم تُمثّل مُعرّفات صالحة للمنتجات، يمكنك إنشاء قيد مفتاح خارجي عليه بحيث يُشير إلى عمود productID في جدول المنتجات products. يُعد قيد المفتاح الخارجي طريقة لتحديد علاقة بين جدولين، إذ يفرض أن تكون القيم في العمود المُطبّق عليه موجودة بالفعل في العمود المُشار إليه.
يشترط قيد المفتاح الخارجي في تعليمة CREATE TABLE أدناه أن تكون كل قيمة تُضاف إلى عمود productSpecialty من جدول الفريق team موجودة مسبقًا في عمود productID من جدول المنتجات products.
أنشئ جدولًا باسم team يحتوي على هذه الأعمدة الثلاثة:
mysql> CREATE TABLE team ( mysql> empID int UNIQUE, mysql> empName varchar(20), mysql> productSpecialty int, mysql> PRIMARY KEY (empID), mysql> FOREIGN KEY (productSpecialty) REFERENCES products (productID) );
أمّا الجدول الأخير فسيتضمّن سجلات مبيعات الشركة. وسيكون لهذا الجدول أربعة أعمدة:
-
saleID: مشابه لعموديproductIDوempID، إذ سيحتوي هذا العمود على مُعرّف فريد لكل عملية بيع مُعبرًا عنه بنمط البياناتint. سنطبق على هذا العمود أيضًا قيدUNIQUEليلعب دور المفتاح الأساسي لجدول المبيعاتsales. -
quantity: عدد الوحدات من كل منتج مُباع، مُعبرًا عنه بنمط البياناتint. -
productID: مُعرّف المنتج المُباع، مُعبرًا عنه بنمط البياناتint. -
salesperson: مُعرّف الموظف الذي أجرى عملية البيع.
وعلى نحوٍ مشابه لعمود productSpecialty من جدول الفريق team، لنُطبّق قيد FOREIGN KEY على كل من عموديّ productID وsalesperson. الأمر الذي سيضمن أنّ هذه الأعمدة لن تتضمّن سوى قيم موجودة بالفعل في عمود productID من جدول المنتجات products وعمود empID من جدول الفريق team على التوالي.
لننشئ إذًا جدولًا باسم sales يحتوي على هذه الأعمدة الأربعة:
mysql> CREATE TABLE sales ( mysql> saleID int UNIQUE, mysql> quantity int, mysql> productID int, mysql> salesperson int, mysql> PRIMARY KEY (saleID), mysql> FOREIGN KEY (productID) REFERENCES products (productID), mysql> FOREIGN KEY (salesperson) REFERENCES team (empID) mysql> );
ومن ثم املأ جدول المنتجات products ببعض البيانات التجريبية النموذجية عبر تنفيذ عملية INSERT INTO التالية:
mysql> INSERT INTO products mysql> VALUES mysql> (1, 'widget', 18.99), mysql> (2, 'gizmo', 14.49), mysql> (3, 'thingamajig', 39.99), mysql> (4, 'doodad', 11.50), mysql> (5, 'whatzit', 29.99);
ثم املأ جدول الفريق team ببعض البيانات التجريبية النموذجية:
mysql> INSERT INTO team mysql> VALUES mysql> (1, 'Florence', 1), mysql> (2, 'Mary', 4), mysql> (3, 'Diana', 3), mysql> (4, 'Betty', 2);
املأ جدول المبيعات sales ببعض البيانات النموذجية أيضًا:
mysql> INSERT INTO sales mysql> VALUES mysql> (1, 7, 1, 1), mysql> (2, 10, 5, 4), mysql> (3, 8, 2, 4), mysql> (4, 1, 3, 3), mysql> (5, 5, 1, 3);
ونهايةً، تخيّل أن شركتك حققت بعضًا من المبيعات دون مشاركة أحد من فريق المبيعات. لتسجيل هذه المبيعات، أضف ثلاث سجلات إلى جدول المبيعات sales لا تتضمن قيمة لعمود موظف المبيعات salesperson عبر تنفيذ العملية التالية:
mysql> INSERT INTO sales (saleID, quantity, productID) mysql> VALUES mysql> (6, 1, 5), mysql> (7, 3, 1), mysql> (8, 4, 5);
بهذا، تغدو مستعدًا لمتابعة باقي المقال وبدء تعلم كيفية دمج الجداول معًا في SQL.
فهم صيغة عمليات بنى الدمج JOIN
يمكن استخدام بنى الدمج JOIN في مجموعة متنوعة من تعليمات SQL، بما في ذلك عمليات التحديث UPDATE والحذف DELETE. ولكن ولأغراض التوضيح، سنستخدم في الأمثلة في هذا المقال استعلامات SELECT لإظهار كيفية عمل بنى الدمج JOIN.
يُظهر المثال التالي الصيغة العامة لتعليمة SELECT التي تتضمن بنية الدمج JOIN:
mysql> SELECT table1.column1, table2.column2 mysql> FROM table1 JOIN table2 mysql> ON search_condition;
تبدأ هذه الصيغة بتعليمة SELECT التي ستعيد عمودين من جدولين منفصلين. لاحظ أنه نظرًا لقدرة بنى JOIN على مقارنة البيانات من عدة جداول، فإن صيغة هذا المثال تُوضّح الجدول المُستهدف لكل عمود بوضع اسم الجدول متبوعًا بنقطة قبل اسم العمود، وهذا ما يُعرف بالإشارة الكاملة والمؤهلة للعمود fully qualified column reference.
يمكنك استخدام الإشارة الكاملة والمؤهلة للعمود في أي عملية على نحوٍ اختياريّ، في حين يغدو استخدامها ضرورةً فقط في العمليات التي يكون فيها عمودان يشتركان في نفس الاسم من جداول مختلفة. ومن الجيد عمومًا استخدامها لدى التعامل مع جداول متعددة، إذ يمكن أن تساعد في جعل عمليات الدمج JOIN أسهل للقراءة والفهم.
وتأتي بنية FROM بعد بنية SELECT. إذ تُحدد بنية FROM في أي استعلام مجموعة البيانات التي ينبغي البحث فيها لإعادة البيانات المطلوبة. ولعلّ الاختلاف الوحيد هنا هو أن بنية FROM تتضمن جدولين مفصولين بالكلمة المفتاحية JOIN. ومن الطرق المفيدة في فهم الاستعلامات لدى كتابتها هي تذكر أنك تختار (SELECT) الأعمدة التي تريد إعادتها من (FROM) الجدول الذي ترغب في الاستعلام عنه.
يلي ذلك بنية ON، والتي تصف كيفية ربط الاستعلام للجدولين معًا عن طريق تحديد شرط بحث. وما شرط البحث سوى مجموعة من التوابع الشرطية أو التعابير القادرة على تقييم فيما إذا كان شرط معيّن "محققًا True" أو "غير محقق False" أو "غير محدد Unknown". وبالتالي، من المفيد فهم عملية الدمج JOIN على أنها عملية تجميع كافة السجلات من جدولين، لتعيد بعد ذلك أي سجلات يُقيم شرط البحث في بنية ON من أجلها على أنه "محقق True".
ولعلّه من المنطقي في بنية ON أن نُضمّن شرط بحث يختبر ما إذا كان عمودين مرتبطين – من قبيل مفتاح خارجي لجدول ما ومفتاح أساسي لجدول آخر يُشير إليه ذلك المفتاح الخارجي - يتضمنان قيمًا متساوية. وهذا ما يُشار إليه أحيانًا بالدمج عند التساوي equi join.
وكمثال على كيفية مطابقة "الدمج عند التساوي" للبيانات من جداول متعددة، لنُنفّذ الاستعلام التالي باستخدام البيانات التجريبية النموذجية المُضافة سابقًا. إذ ستعمل هذه التعليمة على دمج جدولي products وteam باستخدام شرط بحث يختبر تطابق القيم في عمودي productID وproductSpecialty من الجدولين آنفي الذكر، معيدًا أسم كل عضو من فريق المبيعات واسم المنتج المُخصص له وسعر هذا المنتج:
mysql> SELECT team.empName, products.productName, products.price mysql> FROM products JOIN team mysql> ON products.productID = team.productSpecialty;
وتكون مجموعة نتائج هذا الاستعلام على النحو:
الخرج +----------+-------------+-------+ | 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)
ولتوضيح كيفية دمج SQL لهذه الجداول وتشكيل مجموعة النتائج، دعونا نلقي نظرة أقرب على هذه العملية. ومن المهم في هذه المرحلة التنويه إلى كون الخطوات التالية لا نُمثّل بالضبط ما يحدث عند دمج جدولين في نظام إدارة قواعد البيانات، ولكن من المفيد تصوّر عمليات الدمج JOIN وكأنها تتبع خطوات مشابهة لها.
أولًا، يعرض الاستعلام كافة سجلات وأعمدة الجدول الأول ضمن بنية FROM، وهو في حالتنا الجدول products:
مثال على عملية الدمج +-----------+-------------+-------+ | productID | productName | price | +-----------+-------------+-------+ | 1 | widget | 18.99 | | 2 | gizmo | 14.49 | | 3 | thingamajig | 39.99 | | 4 | doodad | 11.50 | | 5 | whatzit | 29.99 | +-----------+-------------+-------+
بعد ذلك، يخضع كل سجل من جدول products للتحليل ليُطابق مع أي سجل من جدول الفريق team حيث تكون قيمة العمود productSpecialty مطابقة لقيمة productID في السجل المعني:
مثال على عملية الدمج +-----------+-------------+-------+-------+----------+------------------+ | 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 | | | | +-----------+-------------+-------+-------+----------+------------------+
تُستبعد بعد ذلك كافة السجلات التي لا تتطابق فيها قيمة العمود productSpecialty مع قيمة productID، ثم يُعاد تنظيم الأعمدة وفق ترتيب ورودها ضمن بنية SELECT، مع حذف أي أعمدة لم تُحدد في الاستعلام، وأخيرًا يُعاد فرز السجلات المتبقية وتُقدم كمجموعة النتائج النهائية:
مثال على عملية الدمج +----------+-------------+-------+ | 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)
ولعلّ استخدام الدمج عند التساوي هو الأسلوب الأكثر شيوعًا لربط الجداول، في حين من الممكن استخدام عوامل SQL أخرى ضمن شروط بحث بنية ON، من قبيل <، >، LIKE، NOT LIKE، أو حتى BETWEEN. ويُنصح بالانتباه إلى أن استخدام شروط بحث أعقد قد يجعل التنبؤ بالبيانات التي ستظهر في مجموعة النتائج أصعب.
يُمكنك دمج الجداول في معظم تقديمات SQL باستخدام أي مجموعة من الأعمدة، شرط امتلاكها لما يُشار إليه في المعيار القياسي لـ SQL باسم "نمط بيانات مؤهل للدمج". ما يعني عمومًا أنّه من الممكن دمج عمود يحتوي على بيانات رقمية مع أي عمود آخر يحمل بيانات رقمية، وذلك بغض النظر عن أنماط بياناتهما الدقيقة. وبالمثل، من الممكن عادةً دمج أي عمود يحتوي على قيم محرفية مع آخر يحمل بيانات محرفية. ولكن وكما ذكرنا سابقًا، عادةً ما تكون الأعمدة التي نختارها لدمج جدولين هي تلك التي تُشير بالفعل إلى علاقة بين الجداول، من قبيل مفتاح خارجي مع المفتاح الأساسي لجدول آخر والذي يُشير إليه ذلك المفتاح الخارجي.
كما تسمح العديد من تقديمات SQL بدمج الأعمدة التي تحمل نفس الاسم باستخدام الكلمة المفتاحية USING بدلًا من ON. وتبدو صيغة مثل هذه العملية على النحو الآتي:
mysql> SELECT table1.column1, table2.column2 mysql> FROM table1 JOIN table2 mysql> USING (related_column);
في صيغة هذا المثال، تعادل بنية USING استخدام ON table1.related_column = table2.related_column;.
وبما أنّ لكل من جدولي sales وproducts عمود باسم productID، فمن الممكن دمجهما عبر مطابقة هذه الأعمدة باستخدام الكلمة المفتاحية USING. وهذا ما يُمثّل مهمّة الأمر التالي، والذي يُعيد saleID لكل عملية بيع، وكمية الوحدات المباعة، واسم كل منتج تم بيعه وسعره. كما يفرز مجموعة النتائج تصاعديًا استنادًا إلى قيمة saleID:
mysql> SELECT sales.saleID, sales.quantity, products.productName, products.price mysql> FROM sales JOIN products mysql> USING (productID) mysql> ORDER BY saleID;
الخرج +--------+----------+-------------+-------+ | 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)
إذ قد يعيد نظام قاعدة البيانات أحيانًا ترتيب السجلات بطرق لا يسهل التنبؤ بها عند دمج الجداول. ولعلّ الحل يكون بتضمين جملة ORDER BY كما في المثال أعلاه، ما قد يُساعد في جعل مجموعات النتائج أكثر تناسقًا وسهولة في القراءة.
دمج أكثر من جدولين
نحتاج أحيانًا إلى دمج البيانات من أكثر من جدولين. إذ يُمكننا دمج أي عدد من الجداول معًا عن طريق تضمين بنى دمج JOIN ضمن بنى JOIN أخرى. وتُمثّل الصيغة التالية مثالًا لحالة دمج ثلاثة جداول:
mysql> SELECT table1.column1, table2.column2, table3.column3 mysql> FROM table1 JOIN table2 mysql> ON table1.related_column = table2.related_column mysql> JOIN table3 mysql> ON table3.related_column = table1_or_2.related_column;
تبدأ صيغة البنية FROM في هذا المثال بدمج الجدول table1 مع table2. لتبدأ عملية دمج JOIN ثانية بعد بنية ON الخاصة بعملية الدمج الأولى، والتي تدمج مجموعة الجداول المدموجة الأولية السابقة مع الجدول table3. ونلاحظ هنا أنه من الممكن دمج الجدول الثالث مع عمود موجود إما في الجدول الأول أو الثاني.
ولتوضيح الأمر، لنفرض أننا نرغب بمعرفة قيمة الإيرادات المُحققة من مبيعات الموظفين، لكن اهتمامنا منصب فقط على سجلات المبيعات التي تشمل موظفًا باع المنتج الذي يتخصص فيه تحديدًا دون مبيعاته لأي منتج آخر.
وللحصول على هذه المعلومات، يمكننا تنفيذ الاستعلام التالي. والذي يبدأ بدمج جدولي products وsales معًا عن طريق مطابقة عمودي productID في كل منهما. ثم يدمج جدول team مع الجدولين السابقين بمطابقة كل سجل من عملية الدمج الأولية بعمود productSpecialty لكل موظف. بعد ذلك، يعمل الاستعلام على تصفية النتائج باستخدام بنية WHERE بهدف إعادة السجلات التي يكون فيها الموظف، الذي تم تخصيصه للمنتج المُباع، هو نفسه من أتم عملية البيع بالفعل. يتضمن هذا الاستعلام أيضًا بنية ORDER BY التي تفرز النتائج النهائية تصاعديًا استنادًا إلى القيم في عمود saleID.
mysql> SELECT sales.saleID, mysql> team.empName, mysql> products.productName, mysql> (sales.quantity * products.price) mysql> FROM products JOIN sales mysql> USING (productID) mysql> JOIN team mysql> ON team.productSpecialty = sales.productID mysql> WHERE team.empID = sales.salesperson mysql> ORDER BY sales.saleID;
نلاحظ أنّه من بين الأعمدة المدرجة في بنية SELECT لهذا الاستعلام يوجد تعبير يضرب قيم عمود quantity الموجود في جدول sales بقيم السعر الموجودة في عمود price بجدول products. والذي يُعيد حاصل ضرب هذه القيم في السجلات المتطابقة.
الخرج +--------+----------+-------------+-----------------------------------+ | 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)
استعرضت كافّة الأمثلة حتى الآن نفس نوع بنية الدمج، ألا وهي: الدمج الداخلي INNER JOIN. وللحصول على نظرة عامّة حول كل من الدمج الداخلي والدمج الخارجي OUTER JOIN وأوجه اختلافهما، تابع قراءة القسم التالي.
عمليات الدمج الداخلي مقابل الدمج الخارجي
يوجد نوعان رئيسيان من بنى الدمج: الدمج الداخلي INNER والدمج الخارجي OUTER. ويكمن الفارق بين هذين النوعين من الدمج بالبيانات التي يعيدها كل منهما. إذ تُعيد عمليات الدمج الداخلي INNER فقط السجلات التي تمتلك تطابقات من كل جدول مدمج، في حين تُعيد عمليات الدمج الخارجي OUTER السجلات مع أو بدون تطابقات.
استخدمنا في صيغ الأمثلة والاستعلامات من الأقسام السابقة بنى الدمج الداخلي رغم عدم تضمين الكلمة المفتاحية INNER صراحةً في أي منها. فمعظم تطبيقات SQL تعامل أي بنية دمج على أنها INNER ما لم يُذكر خلاف ذلك صراحةً.
تجمع الاستعلامات التي تستخدم الدمج الخارجي OUTER JOIN بين عدة جداول لتعيد السجلات مع أو بدون تطابقات. وهذا الأمر مفيد في الكشف عن السجلات التي تنقصها بعض القيم، أو في الحالات التي تكون فيها التطابقات الجزئية مقبولة.
يمكن تقسيم عمليات الدمج الخارجي OUTER JOIN إلى ثلاثة أنواع فرعية، وهي: الدمج الخارجي الأيسر LEFT OUTER، والدمج الخارجي الأيمن RIGHT OUTER، والدمج الخارجي الكامل FULL OUTER.
يعيد الدمج الخارجي الأيسر، أو ببساطة الدمج الأيسر LEFT JOIN جميع السجلات التي تمتلك تطابقات من كلا الجدولين المدمجين بالإضافة إلى السجلات دون تطابقات من الجدول الموجود على الجانب الأيسر. إذ يعدّ الجدول "الأيسر" في سياق عمليات الدمج ذلك الجدول الأوّل المُحدّد مباشرةً بعد الكلمة المفتاحية FROM وقبل كلمة JOIN. وبالمثل، يعدّ الجدول "الأيمن" هو الجدول الثاني، أو الجدول الذي يلي كلمة JOIN مباشرةً، ويُعيد الدمج الخارجي الأيمن RIGHT OUTER كافة السجلات ذات التطابقات من الجداول المدمجة بالإضافة إلى كل سجل دون تطابقات من الجدول "الأيمن". في حين يعيد الدمج الخارجي الكامل FULL OUTER JOIN كافة السجلات من كلا الجدولين، بما في ذلك تلك التي لا تمتلك أي مطابقات.
لتوضيح كيف تعيد هذه الأنواع المختلفة من بنى الدمج البيانات، لننفذ الاستعلامات التالية على الجداول المُنشأة في الفقرة السابقة "الاتصال بـ MySQL وإعداد قاعدة بيانات تجريبية نموذجية". مع ملاحظة أن هذه الاستعلامات متطابقة باستثناء أن كل واحد منها يستخدم نوعًا مختلفًا من بنى الدمج JOIN.
يستخدم هذا المثال الأول الدمج الداخلي INNER JOIN لدمج جدولي sales وteam معًا بمطابقة عمودي salesperson وempID من كل منهما على التوالي. ونؤكّد مجددًا أنّ الاستعلام يعدّ داخليًا حتى وإن لم نُضمّن الكلمة المفتاحية INNER صراحةً ما لم نشير إلى خلاف ذلك ضمن بنية الاستعلام.
mysql> SELECT sales.saleID, sales.quantity, sales.salesperson, team.empName mysql> FROM sales JOIN team mysql> ON sales.salesperson = team.empID;
نظرًا لأن هذا الاستعلام يستخدم بنية الدمج الداخلي INNER JOIN، فإنه يُعيد فقط السجلات ذات التطابقات من كلا الجدولين:
الخرج +--------+----------+-------------+----------+ | 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)
سنستخدم الآن في هذه النسخة من الاستعلام بنية الدمج الخارجي الأيسر LEFT OUTER JOIN، على النحو:
mysql> SELECT sales.saleID, sales.quantity, sales.salesperson, team.empName mysql> FROM sales LEFT OUTER JOIN team mysql> ON sales.salesperson = team.empID;
وكما هو الحال مع الاستعلام السابق، يعيد هذا الاستعلام أيضًا كافّة القيم ذات التطابقات من كلا الجدولين. لكنه يعيد أيضًا أي قيم من الجدول "الأيسر" (وهو الجدول sales في هذه الحالة) التي لا تمتلك أي تطابقات مع الجدول "الأيمن" (team). ولكن وبما أنّ هذه السجلات في الجدول الأيسر لا تمتلك تطابقات في الجدول الأيمن، فتُعاد القيمة الفارغة NULL بدلًا من القيم غير المتطابقة من الجدول الأيمن.
الخرج +--------+----------+-------------+----------+ | 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)
أمّا الآن فسنستخدم بنية الدمج الأيمن RIGHT JOIN، على النحو:
mysql> SELECT sales.saleID, sales.quantity, sales.salesperson, team.empName mysql> FROM sales RIGHT JOIN team mysql> ON sales.salesperson = team.empID;
لاحظ أننا اكتفينا باستخدام التعليمة RIGHT JOIN بدلًا من RIGHT OUTER JOIN في بنية الدمج من هذا الاستعلام. فكما هو الحال من حيث عدم اشتراط استخدام الكلمة المفتاحية INNER لتحديد بنية دمج داخلي INNER JOIN، فإن كلمة OUTER تُطبّق تلقائيًا بدورها بمجرد كتابة LEFT JOIN أو RIGHT JOIN.
نتيجة هذا الاستعلام هي العكس تمامًا من الاستعلام السابق، بمعنى أنها تعيد كافّة السجلات ذات التطابقات من كلا الجدولين، والسجلات دون تطابقات من الجدول "الأيمن" فقط:
الخرج: +--------+----------+-------------+----------+ | 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)
ملاحظة: انتبه لكون MySQL لا تدعم بنى الدمج الخارجي الكامل FULL OUTER JOIN. ولتوضيح البيانات التي كان من الممكن أن يُعيدها هذا الاستعلام في حال استخدام بنية FULL OUTER JOIN، إليك كيف ستبدو مجموعة النتائج في قاعدة بيانات PostgreSQL:
Joinsdb=# SELECT sales.saleID, sales.quantity, sales.salesperson, team.empName Joinsdb=# FROM sales FULL OUTER JOIN team Joinsdb=# ON sales.salesperson = team.empID;
الخرج
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 | |
7 | 3 | |
8 | 4 | |
| | | Mary
(9 rows)
نلاحظ من النتائج أعلاه أنّ الدمج الكامل FULL JOIN يُعيد كافّة السجلات من كلا الجدولين بما في ذلك تلك التي لا تمتلك تطابقات.
تسمية الجداول والأعمدة بأسماء بديلة في بنى الدمج
لدى دمج جداول ذات أسماء طويلة أو وصفية للغاية، قد تصبح مهمة كتابة الإشارة الكاملة والمؤهلة للعمود مرهقة. ولتجنب ذلك، يجد المستخدمون أحيانًا أنّه من المفيد تزويد أسماء الجداول أو الأعمدة بأسماء بديلة أقصر.
الأمر الذي يمكننا تنفيذه في SQL بإلحاق تعريف الجدول في بنية FROM بكلمة AS المفتاحية، ليأتي بعدها الاسم البديل الذي اخترناه، على النحو التالي:
mysql> SELECT t1.column1, t2.column2 mysql> FROM table1 AS t1 JOIN table2 AS t2 mysql> ON t1.related_column = t2.related_column;
استخدمنا في صيغة هذا المثال الأسماء البديلة في بنية SELECT رغم أننا لم نُعرف هذه الأسماء إلّا في بنية FROM. وهذا الأمر ممكن لأنّ ترتيب تنفيذ الاستعلامات في SQL يبدأ ببنية FROM أولًا. قد تكون هذه الطريقة مربكة، ولكن من المفيد تذكرها والتفكير في الأسماء البديلة قبل البدء في كتابة الاستعلام.
كمثال، لننفذ الاستعلام التالي الذي يدمج جدولي sales وproducts ويزودهما بالأسماء البديلة S وP على التوالي:
mysql> SELECT S.saleID, S.quantity, mysql> P.productName, mysql> (P.price * S.quantity) AS revenue mysql> FROM sales AS S JOIN products AS P mysql> USING (productID);
نلاحظ أن هذا المثال يُنشئ أيضًا اسمًا بديلًا ثالثًا وهو revenue لحاصل ضرب قيم العمود quantity من الجدول sales بقيمها المقابلة في عمود price من الجدول products. ولن يظهر هذا الاسم سوى ضمن مجموعة النتائج كاسم عمود، لكنه مهم لتوضيح المعنى أو الغرض من نتائج الاستعلام:
الخرج +--------+----------+-------------+---------+ | 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)
وتجدر الملاحظة إلى أنّ استخدام كلمة AS عند تحديد الأسماء البديلة اختياري من الناحية التقنية، إذ من الممكن أيضًا كتابة المثال السابق على النحو:
mysql> SELECT S.saleID, S.quantity, P.productName, (P.price * S.quantity) revenue mysql> FROM sales S JOIN products P mysql> USING (productID);
ورغم كون كلمة AS ليست ضرورية لتحديد اسم بديل، إلّا أنّ تضمينها يعدّ من الممارسات الجيدة. إذ يُمكن أن يساعد في الحفاظ على وضوح غرض الاستعلام وتحسين مقروئيته.
الخلاصة
باطلاعك على هذا المقال، اكتسبت المعرفة حول كيفية استخدام عمليات الدمج JOIN لدمج جداول متفرقة ضمن مجموعة نتائج استعلام واحدة.
ومن المفترض أن تعمل الأوامر المشروحة في هذا المقال مع أي نظام لإدارة قواعد البيانات يستخدم SQL. لكن تذكر أن لكل قاعدة بيانات SQL تقديمها الخاص للغة، لذا ينبغي مراجعة التوثيق الرسمي لنظام إدارة قواعد البيانات الخاص بك للحصول على وصف أكثر تفصيلاً لكل أمر فيها ومجموعة خياراته الكاملة. وللمزيد حول SQL، نشجعك على متابعة سلسلة تعلم SQL في أكاديمية حسوب.
ترجمة -وبتصرف- للمقال How To Use Joins in SQL لصاحبه Mark Drake.
أفضل التعليقات
لا توجد أية تعليقات بعد
انضم إلى النقاش
يمكنك أن تنشر الآن وتسجل لاحقًا. إذا كان لديك حساب، فسجل الدخول الآن لتنشر باسم حسابك.