سلسلة sql للمحترفين الدمج بين الجداول في SQL

تدمج العبارة JOIN البيانات من جدولين، وتعيد مجموعة مختلطة من الأعمدة من كلا الجدولين، وذلك حسب نوع الضمّ المُستخدم، ومعاييره (كيفية ربط الصفوف من كلا الجدولين).

يمكن ضمّ جدولٍ إلى نفسه، أو بأيّ جدول آخر. وإذا كانت هناك حاجة للوصول إلى معلومات من أكثر من جدولين، فيمكن استخدام الضمّ عدّة مرّات في عبارة FROM.

الضمّ الذاتي Self Join

يمكن ضمّ جدول إلى نفسه، بحيث تتطابق الصفوف مع بعضها البعض وفق شروط معينة. في مثل هذه الحالة، يجب استخدام الكُنى (aliases) للتمييز بين العناصر المكرّرة من الجدول.

في المثال التالي، لكلّ موظّف في جدول الموظّفين Employees، يُعاد سجلّ يحتوي الاسم الأول للموظّف، والاسم الأول لمديره. ولمّا كان المدراء هم أيضًا موظفين، فسنضمّ الجدول إلى نفسه:

SELECT
    e.FName AS "Employee",
    m.FName AS "Manager"
FROM 
    Employees e
JOIN 
    Employees m
   ON e.ManagerId = m.Id

سيعيد هذا الاستعلام البيانات التالية:

شرح الاستعلام

يحتوي الجدول الأصلي على هذه السجلات:

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

الخطوة التالية هي ترشيح السجلات، والإبقاء على السجلات التي تفي بشرط الضمّ وحسب، أي سجلات الجدول ‎e‎ التي يساوي الحقل ‎ManagerId‎ خاصتها الحقلَ ‎Id‎ في الجدول ‎m‎:

بعد ذلك، تُقيّم كل التعبيرات المستخدمة في عبارة SELECT لإعادة الجدول التالي:

أخيرًا، يُستبدل اسما العمودين ‎e.FName‎ و ‎m.FName‎ بكُنيتيهما:

الاختلاف بين الضم الداخلي والخارجي

هناك عدّة أنواع من الضمّ في SQL، وتختلف تلك الأنواع عن بعضها من حيث ما إذا كانت الصفوف التي (لا) تحقّق الشرط ستُضمّ أم لا.

هذه بعض أهمّ أنواع الضمّ: ‎INNER JOIN‎ و ‎LEFT‎ OUTER‎‎JOINوRIGHT OUTER JOIN‎و‎FULL OUTER ‎JOIN‎(الكلمتان المفتَاحيتان‎INNER‎و‎OUTER‎` اختياريتان).

يوضّح الشكل أدناه الاختلافات بين مختلف أنواع الضمّ: تمثل المنطقة الزرقاء النتائج المُعادة من عملية الضمّ، فيما تمثّل المنطقة البيضاء النتائج التي لن تعيدها عملية الضمّ.

وهذه صورة لتمثيل الضمّ المتقاطع (Join SQL)

مصدر الصورة

على سبيل المثال، إليك الجدولين التاليين:

A    B
-    -
1    3
2    4
3    5
4    6

لاحظ أنّ القيمتين (1،2) حصريتان للجدول A، أمّا القيمتان (3،4) فمُشتركتان، و القيمتان (5،6) حصريتان لـ B.

الضمّ الداخلي

يعيد الضم الداخلي تقاطع الجدولين، أي الصفوف المشترك بينهما:

select * from a INNER JOIN b on a.a = b.b;
select a.*,b.* from a,b where a.a = b.b;
a | b
--+--
3 | 3
4 | 4

الضم الخارجي اليساري Left outer join

يعيد الضم الخارجي اليساري جميع صفوف A، بالإضافة إلى الصفوف المشتركة مع B:

select * from a LEFT OUTER JOIN b on a.a = b.b;
a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

الضم الخارجي اليميني Right outer join

وبالمثل، يعيد الضمّ الخارجي اليميني كل صفوف B، بالإضافة إلى الصفوف المشتركة في A:

select * from a RIGHT OUTER JOIN b on a.a = b.b;
a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

الضمّ الخارجي التام Full outer join

يعيد الضمّ الخارجي التام اتحاد A و B، أي جميع الصفوف الموجودة في A وجميع الصفوف الموجودة في B. إذا كانت هناك بيانات في A بدون بيانات مقابلة في B، فسيكون الجزء الخاص بـ B معدوما (null). والعكس صحيح.

select * from a FULL OUTER JOIN b on a.a = b.b;
a   |  b
-----+-----
1 | null
2 | null
3 |    3
4 |    4
null |    6
null |    5

اصطلاحات الضمّ JOIN Terminology

لنفترض أنّ لدينا جدولين A و B، وأنّ بعض صفوفِهما متطابقة (وفق شرط JOIN):

هناك عدّة أنواع مختلفة من الضمّ يمكن استخدامها لأجل تضمين أو استبعاد الصفوف التي (لا) تحقق شرط الضمّ في كلا الجانبين.

تستخدم الأمثلة أدناه البيانات التالية:

CREATE TABLE A (
    X varchar(255) PRIMARY KEY
);
CREATE TABLE B (
    Y varchar(255) PRIMARY KEY
);
INSERT INTO A VALUES
    ('Amy'),
    ('John'),
    ('Lisa'),
    ('Marco'),
    ('Phil');
INSERT INTO B VALUES
    ('Lisa'),
    ('Marco'),
    ('Phil'),
    ('Tim'),
    ('Vincent');

الضمّ الداخلي Inner Join

يجمع الضمّ الداخلي بين الصفوف اليسرى واليمنى المتطابقة.

SELECT * FROM A JOIN B ON X = Y;
X      Y
------ -----
Lisa   Lisa
Marco  Marco
Phil   Phil

الضم الخارجي اليساري Left outer join

يُسمّى اختصارًا الضمّ اليساري. ويجمع بين الصفوف اليسرى واليمنى التي تحقّق الشرط، مع تضمين الصفوف اليسرى التي لا تحقّق الشرط.

SELECT * FROM A LEFT JOIN B ON X = Y;
X         Y
-----     -----
Amy       NULL
John      NULL
Lisa      Lisa
Marco     Marco
Phil      Phil

الضم الخارجي اليميني Right outer join

يُسمّى اختصارًا الضمّ الأيمن. ويجمع بين الصفوف اليسرى واليمنى التي تحقّق الشرط، مع تضمين الصفوف اليمنى التي لا تحقّق الشرط.

SELECT * FROM A RIGHT JOIN B ON X = Y;
X          Y
-----      -------
Lisa       Lisa
Marco      Marco
Phil       Phil
NULL       Tim
NULL       Vincent

الضمّ الخارجي التام Full outer join

يُسمّى اختصارًا الضمّ التام. وهو اتحاد لعمليتي الضم اليساري واليميني.

SELECT * FROM A FULL JOIN B ON X = Y;
X          Y
-----      -------
Amy        NULL
John       NULL
Lisa       Lisa
Marco      Marco
Phil       Phil
NULL       Tim
NULL       Vincent

الضمّ شبه اليساري

يضمّ هذا النوع الصفوفَ اليُسرى التي تتطابق مع الصفوف اليمنى.

SELECT * FROM A WHERE X IN (SELECT Y FROM B);
X
-----
Lisa
Marco
Phil

الضمّ شبه اليميني Right Semi Join

يضمّ هذا النوع الصفوف اليمنى التي تطابق الصفوف اليسرى.

SELECT * FROM B WHERE Y IN (SELECT X FROM A);
Y
-----
Lisa
Marco
Phil

لا توجد صياغة للعبارة IN مُخصّصة للضمّ شبه اليساري أو شبه اليميني - كلّ ما عليك فعله هو تبديل مواضع الجدول في SQL.

الضمّ شبه اليساري المعكوس Left Anti Semi Join

يُضمِّن هذا النوع الصفوفَ اليُسرى التي لا تتطابق مع الصفوف اليمنى.

SELECT * FROM A WHERE X NOT IN (SELECT Y FROM B);
X
----
Amy
John

تنبيه: استخدام NOT IN في الأعمدة التي تقبل القيم المعدومة NULL قد يسبّب بعض المشاكل (المزيد من التفاصيل هنا).

الضمّ شبه اليميني المعكوس Right Anti Semi Join

يُضمِّن هذا النوع الصفوف اليمنى التي لا تطابق الصفوف اليسرى.

SELECT * FROM B WHERE Y NOT IN (SELECT X FROM A);
Y
-------
Tim
Vincent

لا توجد صياغة للعبارة IN مخصّصة للضمّ شبه اليساري أو شبه اليميني المعكوس - كلّ ما عليك فعله هو تبديل مواضع الجدول في SQL.

الضم المتقاطع Cross Join

يُجري هذا النوع من الضمّ جداءً ديكارتيًا (Cartesian product) بين الصوف اليسرى والصفوف اليمنى.

SELECT * FROM A CROSS JOIN B;
X         Y
-----     -------
Amy       Lisa
John      Lisa
Lisa      Lisa
Marco     Lisa
Phil      Lisa
Amy       Marco
John      Marco
Lisa      Marco
Marco     Marco
Phil      Marco
Amy       Phil
John      Phil
Lisa      Phil
Marco     Phil
Phil      Phil
Amy       Tim
John      Tim
Lisa      Tim
Marco     Tim
Phil      Tim
Amy       Vincent
John      Vincent
Lisa      Vincent
Marco     Vincent
Phil      Vincent

يكافئ الضمّ المتقاطع ضمًّا داخليًا ذا شرط يتحقّق دائمًا، لذا سيعيد الاستعلام التالي النتيجة نفسها:

SELECT * FROM A JOIN B ON 1 = 1;

الضمّ الذاتي Self-Join

يشير هذا النوع من الضم إلى ضمّ الجدول إلى نفسه. يمكن أن تكون عملية الضمّ الذاتي من أيّ نوع من أنواع الضمّ التي ناقشناها أعلاه. على سبيل المثال، هذا ضمّ ذاتي داخلي ( inner self-join):

SELECT * FROM A A1 JOIN A A2 ON LEN(A1.X) < LEN(A2.X);
X         X
----      -----
Amy       John
Amy       Lisa
Amy       Marco
John      Marco
Lisa      Marco
Phil      Marco
Amy       Phil

الضمّ الخارجي اليساري Left Outer Join

يضمن الضمّ الخارجي اليساري (المعروف أيضًا باسم الضمّ اليساري أو الضمّ الخارجي) تمثيل جميع صفوف الجدول الأيسر؛ وفي حال عدم وجود صفّ مطابق في الجدول الأيمن، فسيُعطى الحقل المقابل القيمةَ ‎NULL‎.

سيختار المثال التالي جميع الأقسام (departments) والأسماء الأولى للموظّفين الذين يعملون في تلك الأقسام. وستُعاد الأقسام التي لا تحتوي على أيّ موظفين، مع إعطاء اسم الموظف المقابل لها القيمة NULL:

SELECT                    Departments.Name, Employees.FName
FROM                       Departments
LEFT OUTER JOIN  Employees
ON                            Departments.Id = Employees.DepartmentId

سنحصل على الخرج التالي:

شرح الاستعلام

يوجد جدولان في عبارة FROM، وهما:

وهذا هو الجدول الثاني:

في المرحلة الأولى، يُنشأ جداء ديكارتي للجدولين، وينتج عنه جدول وسيط.

يُغلَّظُ خطّ السجلات التي تفي بشرط الضمّ (والذي هو في هذه الحالة: Departments.Id = Employees.DepartmentId)؛ وتُمرَّر إلى المرحلة التالية من الاستعلام.

لمّا كان هذا الضّمّ ضمًّا خارجيًا يساريًا (LEFT OUTER JOIN)، فستُعاد جميع السجلّات الموجودة في الجانب الأيسر من الضمّ (أي الأقسام Departments)، في حين تُعطى السجلات الموجودة على الجانب الأيمن القيمة المعدومة (NULL) في حال لم تُطابق شرط الضمّ.

بعد ذلك، تُقيّم كل التعبيرات المستخدَمة في عبارة SELECT لإعادة الجدول التالي:

الضمّ الضمني Implicit Join

يمكن أيضًا إجراء عملية الضمّ على عدّة جداول، حيث توضع في عبارة ‎from‎ مفصولة بالفاصلة ‎,‎، مع تحديد العلاقة بينها في العبارة ‎where‎. تسمى هذه التقنية "الضمّ الضمني" - Implicit Join - (لأنها لا تحتوي فعليًا العبارةَ ‎join‎).

تدعم جميع أنظمة معالجة قواعد البيانات (RDBMSs) هذه التقنية، ولكن ينصح بتجنّب استخدامها للأسباب التالية:

• قد تتداخل صياغة الضمّ الضمني مع صياغة الضمّ المتقاطع (cross join)، وهو ما قد يؤدي إلى إعادة نتائج غير صحيحة، خاصةً إذا كان الاستعلام يحتوي الكثير من عمليات الضمّ.
• إذا كنت تنوي استخدام الضم المتقاطع، فلن يكون ذلك واضحًا من الصياغة (اكتب CROSS JOIN بدلاً من ذلك)، ومن المحتمل أن يعدّلها شخص ما أثناء صيانة الشيفرة دون أن ينتبه.

سيختار المثال التالي أسماء الموظفين الأولى وكذلك أسماء الأقسام التي يعملون فيها:

SELECT e.FName, d.Name
FROM   Employee e, Departments d
WHERE  e.DeptartmentId = d.Id

سنحصل على الخرج التالي:

الضم المتقاطع CROSS JOIN

يُجري الضمّ المتقاطع جداءً ديكارتيًا (Cartesian product) على جدولين (الجداء الديكارتي هو عملية تُجمِّع كلّ صفّ من الجدول الأول مع كل صفّ من الجدول الثاني).

على سبيل المثال، إذا كان كلّ من الجدولين ‎TABLEA‎ و ‎TABLEB‎ يحتويان 20 صفًا، فستتألّف النتيجة المُعادة من ‎20*20 = 400‎ صفًّا.

إليك المثال التالي:

SELECT d.Name, e.FName
FROM   Departments d
CROSS JOIN Employees e;

سنحصل على الخرج التالي:

يوصى بكتابة CROSS JOIN بشكل صريح إن أردت إجراء ضمّ ديكارتي دفعًا للُّبس.

التطبيق المتقاطع و الضم الحرفي CROSS APPLY & LATERAL JOIN

هناك نوع خاص من الضمّ يُسمّى الضمّ الحرفي LATERAL JOIN (أضيف حديثًا إلى الإصدار 9.3 وما بعده من PostgreSQL)، والذي يُعرف أيضًا باسم التطبيق المتقاطع CROSS APPLY أو التطبيق الخارجي OUTER APPLY في كلّ من SQL Server و Oracle.

الفكرة الأساسية التي ينبني عليها هذا النوع من الضمّ هي أنه سيتم تطبيق دالة (أو استعلام فرعي مضمّن - inline subquery) على كل الصفوف المضمومة.

يتيح هذا التحكم في عملية الضمّ، مثلًا يمكنك الاكتفاء بضمّ أوّل مُدخل يحقّق شرط الضمّ (matching entry) في الجدول الآخر.

يكمن الاختلاف بين الضمّ العادي والضمّ الحرفي في حقيقة أنّه يمكنك استخدام عمود سبق أن ضممته في الاستعلام الفرعي (subquery) الذي طبّقته تقاطعيًا (CROSS APPLY).

هذه صياغة الضم الحرفي.

• PostgreSQL 9.3 والإصدارات الأحدث:

left | right | inner JOIN LATERAL

• SQL Server

CROSS | OUTER APPLY

‎INNER‎ JOIN‎ LATERAL و ‎CROSS‎ APPLY‎ متكافئتان، وكذلك ‎LEFT JOIN LATERAL‎ و ‎OUTER APPLY‎

إليك المثال التالي (الإصدار 9.3 وما بعده من PostgreSQL):

SELECT * FROM T_Contacts
--LEFT JOIN T_MAP_Contacts_Ref_OrganisationalUnit ON MAP_CTCOU_CT_UID = T_Contacts.CT_UID AND
MAP_CTCOU_SoftDeleteStatus = 1
--WHERE T_MAP_Contacts_Ref_OrganisationalUnit.MAP_CTCOU_UID IS NULL -- 989
LEFT JOIN LATERAL
(
 SELECT
              --MAP_CTCOU_UID    
              MAP_CTCOU_CT_UID  
 ,MAP_CTCOU_COU_UID  
 ,MAP_CTCOU_DateFrom
 ,MAP_CTCOU_DateTo  
 FROM T_MAP_Contacts_Ref_OrganisationalUnit
 WHERE MAP_CTCOU_SoftDeleteStatus = 1
 AND MAP_CTCOU_CT_UID = T_Contacts.CT_UID
 /*  
    AND
    (
        (__in_DateFrom <= T_MAP_Contacts_Ref_OrganisationalUnit.MAP_KTKOE_DateTo)
        AND
        (__in_DateTo >= T_MAP_Contacts_Ref_OrganisationalUnit.MAP_KTKOE_DateFrom)
    )
    */
 ORDER BY MAP_CTCOU_DateFrom
 LIMIT 1
) AS FirstOE

وهذا مثال يخصّ SQL-Server:

SELECT * FROM T_Contacts
--LEFT JOIN T_MAP_Contacts_Ref_OrganisationalUnit ON MAP_CTCOU_CT_UID = T_Contacts.CT_UID AND
MAP_CTCOU_SoftDeleteStatus = 1
--WHERE T_MAP_Contacts_Ref_OrganisationalUnit.MAP_CTCOU_UID IS NULL -- 989
-- CROSS APPLY -- = INNER JOIN
OUTER APPLY -- = LEFT JOIN
(
 SELECT TOP 1
 --MAP_CTCOU_UID    
                            MAP_CTCOU_CT_UID  
 ,MAP_CTCOU_COU_UID  
 ,MAP_CTCOU_DateFrom
 ,MAP_CTCOU_DateTo  
 FROM T_MAP_Contacts_Ref_OrganisationalUnit
 WHERE MAP_CTCOU_SoftDeleteStatus = 1
 AND MAP_CTCOU_CT_UID = T_Contacts.CT_UID
 /*  
    AND
    (
        (@in_DateFrom <= T_MAP_Contacts_Ref_OrganisationalUnit.MAP_KTKOE_DateTo)
        AND
        (@in_DateTo >= T_MAP_Contacts_Ref_OrganisationalUnit.MAP_KTKOE_DateFrom)
    )
    */
 ORDER BY MAP_CTCOU_DateFrom
) AS FirstOE

الضم التام FULL JOIN

هناك نوع آخر من الضمّ أقل شهرة من غيره، وهو الضمّ التام FULL JOIN

(ملاحظة: لا تدعم MySQL الضمّ التام)

يعيد الضمّ التام الخارجي FULL OUTER JOIN جميع صفوف الجدول الأيسر، وكذلك جميع صفوف الجدول الأيمن.

ستُدرج صفوف الجدول الأيسر التي ليس لها مُطابِقَات مقابلة في الجدول الأيمن، وكذلك في الحالة المعكوسة.

إليك المثال التالي:

SELECT * FROM Table1
FULL JOIN Table2
    ON 1 = 2

وهذا مثال آخر:

SELECT
      COALESCE(T_Budget.Year, tYear.Year) AS RPT_BudgetInYear
      ,COALESCE(T_Budget.Value, 0.0) AS RPT_Value
FROM T_Budget
FULL JOIN tfu_RPT_All_CreateYearInterval(@budget_year_from, @budget_year_to) AS tYear
      ON tYear.Year = T_Budget.Year

إن كنت تستخدم عمليات الحذف اللينة soft-deletes (والتي لا تحذف البيانات بشكل نهائي)، فسيتعيّن عليك التحقق من حالة الحذف الليّن مرة أخرى في عبارة WHERE (لأنّ سلوك الضمّ التام - FULL JOIN - يتصرف بشكل يشبه الاتحاد UNION)؛

عند إجراء الضمّ التام، سيتعيّن عليك عادةً السماح بـاستخدام القيمة المعدومة NULL في عبارة WHERE؛ وفي حال نسيت ذلك، فسيتصرّف الضمّ كما لو كان ضمًّا داخليًا (INNER join)، وهو ما لا تريده عند إجراء الضمّ التام.

إليك المثال التالي:

SELECT
  T_AccountPlan.AP_UID
 ,T_AccountPlan.AP_Code
 ,T_AccountPlan.AP_Lang_EN
 ,T_BudgetPositions.BUP_Budget
 ,T_BudgetPositions.BUP_UID
 ,T_BudgetPositions.BUP_Jahr
FROM T_BudgetPositions    
FULL JOIN T_AccountPlan
 ON T_AccountPlan.AP_UID = T_BudgetPositions.BUP_AP_UID
 AND T_AccountPlan.AP_SoftDeleteStatus = 1
WHERE (1=1)
AND (T_BudgetPositions.BUP_SoftDeleteStatus = 1 OR T_BudgetPositions.BUP_SoftDeleteStatus IS NULL)
AND (T_AccountPlan.AP_SoftDeleteStatus = 1 OR T_AccountPlan.AP_SoftDeleteStatus IS NULL)

الضم العودي Recursive JOIN

يُستخدم الضمّ العودي عادة للحصول على بيانات من نوع أب-ابن (parent-child data). في SQL، تُقدّم عمليات الضمّ العودية باستخدام تعبيرات الجدول العادية كما يوضّح المثال التالي:

WITH RECURSIVE MyDescendants AS (
 SELECT Name
 FROM People
 WHERE Name = 'John Doe'
 UNION ALL
 SELECT People.Name
 FROM People
 JOIN MyDescendants ON People.Name = MyDescendants.Parent
)
SELECT * FROM MyDescendants;

الضم الداخلي الصريح

يستعلم الضمّ الأولي - basic join (يُسمّى أيضًا الضمّ الداخلي - inner join) عن البيانات من جدولين، حيث تُحدَّد العلاقة بينهما في عبارة ‎join‎.

يستعلم المثال التالي عن أسماء الموظفين (FName) من جدول الموظفين Employees، وأسماء الأقسام التي يعملون فيها (Name) من جدول الأقسام Departments:

SELECT Employees.FName, Departments.Name
FROM   Employees
JOIN   Departments
ON Employees.DepartmentId = Departments.Id

سنحصل على الخرج التالي:

الضم في استعلام فرعي Joining on a Subquery

غالبًا ما يُستخدم الضمّ في الاستعلامات الفرعية (subquery) للحصول على بيانات مُجمّعة (aggregate data) من جدول يحتوي التفاصيل (الجدول الإبن) وعرضها جنبًا إلى جنب مع السجلات من الجدول الأصلي (الجدول الأب).

على سبيل المثال، قد ترغب في الحصول على عدد السجلات الفرعية (child records)، أو متوسط قيم عمود معيّن في السجلات الفرعية، أو الصف ذو القيمة الأكبر أو الأصغر.

يستخدم هذا المثال الكُنى (لتسهيل قراءة الاستعلامات التي تشمل عدّة جداول)، يعطي المثال فكرة عامّة عن كيفية صياغة عمليات ضمّ الاستعلامات الفرعية. إذ يعيد جميع صفوف الجدول الأصلي "Purchase Orders"، مع إعادة الصف الأول وحسب لكل سجلّ أصلي (parent record) من الجدول الفرعي PurchaseOrderLineItems.

SELECT po.Id, po.PODate, po.VendorName, po.Status, item.ItemNo,
      item.Description, item.Cost, item.Price
FROM PurchaseOrders po
LEFT JOIN
 (
 SELECT l.PurchaseOrderId, l.ItemNo, l.Description, l.Cost, l.Price, Min(l.id) as Id
 FROM PurchaseOrderLineItems l
 GROUP BY l.PurchaseOrderId, l.ItemNo, l.Description, l.Cost, l.Price
 ) AS item ON item.PurchaseOrderId = po.Id

ترجمة -وبتصرّف- للفصل Chapter 18: JOIN من الكتاب SQL Notes for Professionals

اقرأ أيضًا:

• المقال التالي: تحديث الجداول في SQL
• المقال السابق: البحث والتنقيب والترشيح في SQL
• النسخة العربية الكاملة لكتاب ملاحظات للعاملين بلغة SQL 1.0.0