الربط بين الجداول في SQL

لماذا يهم

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

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

كيف يطابق الربط بين الصفوف

تكتب اسمَي الجدولين في FROM … JOIN، ثم شرط الربط بعد ON، وهو عادةً علاقة من النوع الذي تعرّفت إليه في درس المفاتيح والعلاقات: المفتاح الأجنبي orders.customer_id يساوي المفتاح الأساسي الذي يشير إليه، وهو هنا customers.customer_id. وتضم النتيجة كل زوج من الصفوف يتحقق فيه الشرط.

في الجدولين أدناه يشير الطلب 104 إلى العميل 9 غير الموجود، وهذا نوع المرجع المكسور الذي يبحث عنه المختبِر. والحرفان c وo اسمان مستعاران (Aliases)، أي اسمان قصيران للجدولين.

-- customers                    orders
-- customer_id  full_name       order_id  customer_id  total
-- 1            Layla           101       1            250.00
-- 2            Omar            102       1             40.00
-- 3            Priya           103       2            120.00
--                              104       9             75.00

SELECT c.customer_id, c.full_name, o.order_id, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.customer_id
ORDER BY o.order_id;
-- 3 rows: customer 1 with orders 101 and 102, customer 2 with order 103

أنواع الربط INNER وLEFT وRIGHT وFULL

تعيد الأنواع الأربعة كلها الأزواج المتطابقة، ولا تختلف إلا في الصفوف غير المطابِقة. والجدول customers، المكتوب أولاً، هو الجدول الأيسر، وorders هو الأيمن.

• INNER JOIN أو JOIN وحده: الأزواج المتطابقة فقط، أي 3 صفوف. ويختفي العميل 3 الذي لا طلبات له، والطلب 104 الذي لا وجود لعميله، دون أي تحذير.
• LEFT JOIN (أو LEFT OUTER JOIN): كل صفوف الجدول الأيسر، أي 4 صفوف. ويبقى العميل 3 مع NULL في أعمدة الطلب.
• RIGHT JOIN (أو RIGHT OUTER JOIN): كل صفوف الجدول الأيمن، أي 4 صفوف. ويبقى الطلب 104 مع NULL في أعمدة العميل.
• FULL JOIN (أو FULL OUTER JOIN): كل صفوف الجدولين، أي 5 صفوف. يدعمه PostgreSQL وSQL Server وOracle Database، ولا يدعمه MySQL، ولا يدعم SQLite الربطين RIGHT وFULL إلا ابتداءً من الإصدار ⁦3.39.0⁩.

SELECT c.customer_id, o.order_id, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;

-- customer_id  order_id  total
-- 1            101       250.00
-- 1            102        40.00
-- 2            103       120.00
-- 3            NULL      NULL     <- kept by LEFT JOIN, dropped by INNER JOIN

جرّبها: منسّق SQL جرّبها: مدقّق استعلامات SQL

كتابة شرط الربط

تأتي معظم نتائج الربط الخاطئة من عبارة ON، والشرط الخاطئ يعيد مع ذلك صفوفاً تبدو معقولة، لذا قارن عدد صفوف كل نتيجة بالعدد الذي تتوقعه.

في الربط الخارجي (Outer Join) يهمّ موضع أي ترشيح إضافي. ففي ON يحدد الترشيح فقط ما يُعدّ مطابقة، فتبقى صفوف الجدول الأيسر غير المطابِقة. أما في WHERE فيُطبَّق بعد الربط، فيحذف الصفوف المملوءة بـ NULL ويحوّل الربط الأيسر بصمت إلى ربط داخلي، كما يُظهر الاستعلامان أدناه. ويُستثنى من ذلك اختبار IS NULL، فهو يُبقي الصفوف المملوءة بـ NULL تحديداً، وعليه يقوم فحص الأيتام في القسم التالي.

• اربط على الأعمدة التي تستخدمها العلاقة: o.customer_id = c.customer_id. فالشرط o.order_id = c.customer_id يعمل أيضاً، لكنه يقرن صفوفاً لا علاقة بينها كلما تساوى الرقمان مصادفةً.
• استخدم كل أعمدة المفتاح المركّب (Composite Key): فإذا كان معرّف العميل فريداً داخل المستأجر (Tenant) فقط، أي داخل المؤسسة العميلة الواحدة في نظام مشترك متعدد المستأجرين، فاربط على tenant_id وcustomer_id معاً، وإلا اقترن الطلب أيضاً بعملاء مستأجرين آخرين يحملون الرقم نفسه.
• توقّع التكرار في الربط من واحد إلى متعدد (One-to-Many): يظهر العميل 1 مرتين لأن له طلبين، فعدّ الصفوف يعدّ الطلبات لا العملاء.
• إغفال الشرط يقرن كل صف بكل صف، وهذا هو الضرب الديكارتي (Cartesian Product) الذي يعيده CROSS JOIN عن قصد: 3 عملاء و4 طلبات تعطي 12 صفاً. ويحدث ذلك مع FROM customers, orders (جدولان تفصل بينهما فاصلة) إذا لم يربط بينهما شرط في WHERE، ويحدث في MySQL مع JOIN بلا ON؛ أما PostgreSQL فيرفض مثل هذا الربط.
• لا تساوي NULL أي قيمة، ولا حتى NULL أخرى، لذا لا يجد الصف الذي قيمة عمود الربط فيه NULL أي مطابقة أبداً: يحذفه الربط الداخلي، ويحتفظ به الربط الأيسر.

-- Every customer, with their orders over 100.00 if any: 3 rows
SELECT c.customer_id, o.order_id, o.total
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id AND o.total > 100.00;
-- 1 | 101 | 250.00     2 | 103 | 120.00     3 | NULL | NULL

-- The same filter in WHERE also removes customer 3: 2 rows
SELECT c.customer_id, o.order_id, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.total > 100.00;
-- 1 | 101 | 250.00     2 | 103 | 120.00

كشف الصفوف اليتيمة بـ LEFT JOIN … IS NULL

الصف اليتيم (Orphan Row) صف فرعي يشير مفتاحه الأجنبي إلى صف أب غير موجود، مثل الطلب 104. ابدأ من الجدول الفرعي، واربطه ربطاً أيسر بالجدول الأب، واحتفظ فقط بالصفوف التي لم يطابقها أب. على قاعدة بيانات سليمة يعيد الاستعلام صفر صفوف، وكل صف يعيده دليل لتقرير خطأ. والشكل نفسه معكوساً يجد آباء ينقصهم أبناء إلزاميون، مثل طلب بلا أصناف.

اختبر IS NULL على المفتاح الأساسي للجدول الأب، فهو لا يكون NULL أبداً عند وجود مطابقة؛ أما العمود الذي يقبل NULL، كرقم هاتف اختياري، فسيُظهر أيضاً طلبات العملاء الذين ليس لهم هاتف. وتظهر كذلك الصفوف الفرعية التي مفتاحها الأجنبي نفسه NULL: وهذا عيب إن كان العمود إلزامياً، وإلا فاستبعدها بـ AND o.customer_id IS NOT NULL.

ويكتب الاستعلام الثاني في كتلة الشيفرة أدناه الفحص نفسه بـ NOT EXISTS. فالاستعلام الذي بين القوسين، ويسمى الاستعلام الفرعي (Subquery)، يُفحص لكل طلب، ويكون NOT EXISTS صحيحاً حين لا يجد أي صف. أما SELECT 1 فتعني فقط أن أعمدة الاستعلام الفرعي لا تهم، وأن المهم هو وجود صف أو عدمه.

قيد المفتاح الأجنبي المفروض يمنع الأيتام، لكنهم يظهرون في الواقع رغم ذلك:

• لم يُصرَّح بالقيد قط، وهذا شائع في المخططات القديمة.
• عُطّلت القيود أثناء استيراد جماعي أو ترحيل.
• لا يفرض SQLite المفاتيح الأجنبية إلا إذا ضُبط PRAGMA foreign_keys = ON للاتصال.
• يقع الأب والابن في قاعدتي بيانات لخدمتين مختلفتين.

-- Orders whose customer does not exist (expected: 0 rows)
SELECT o.order_id, o.customer_id
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;
-- returns order 104 with customer_id 9: an orphan

-- The same check written with NOT EXISTS
SELECT o.order_id, o.customer_id
FROM orders o
WHERE NOT EXISTS (
  SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id
);

جرّبها: مولّد جمل SQL INSERT جرّبها: مولّد مجموعات بيانات SQL

مثال تطبيقي: طلبات مفقودة من سجل الطلبات

بعد ترحيل من نظام متجر قديم، وصلت تذكرة دعم مفادها أن بعض العملاء لا يرون كل طلباتهم في صفحة «طلباتي»، التي تربط orders بـ customers ربطاً داخلياً. ويشتبه المختبِر في طلبات تشير إلى عملاء غير موجودين.

• المتوقع: كل طلب مرحَّل يخص عميلاً موجوداً، فيعيد فحص الأيتام صفر صفوف.
• الفعلي: 13 صفاً، كلها بقيمة customer_id أكبر من 900000، وهو ترقيم النظام القديم. فقد نسخ الترحيل أرقام العملاء القديمة بدلاً من تحويلها إلى المعرّفات الجديدة، فصارت هذه الطلبات لا تخص أي عميل موجود: لا يستطيع الربط الداخلي في الصفحة أن يقرنها بالعميل الذي سجّل دخوله، ولا يظهر أي خطأ.
• التقرير: الاستعلام، ومعرّفات الطلبات الثلاثة عشر، والنتيجة المتوقعة صفر صفوف، والسبب المرجّح، وسؤال: هل يُضاف قيد مفتاح أجنبي؟
• اختبار التأكيد (Confirmation Testing) بعد الإصلاح: يعيد الاستعلام نفسه صفر صفوف، ويرى عميل متأثر الطلبات المفقودة في الصفحة.

-- Orphan check after the migration (expected: 0 rows)
SELECT o.order_id, o.customer_id, o.created_at
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL
ORDER BY o.order_id;

أسئلة شائعة

المراجع

• ISO/IEC 9075-1:2023 — Information technology — Database languages SQL — Part 1: Framework (SQL/Framework)ISO/IEC · 2023الجزء الإطاري من معيار SQL الدولي، الذي تنفّذه لهجات PostgreSQL وMySQL وSQLite وغيرها بدرجات متفاوتة.
• PostgreSQL Documentation: SQL SyntaxThe PostgreSQL Global Development Groupالبنية المعجمية لـ SQL في PostgreSQL: المعرّفات والاقتباس والثوابت النصية والتهريب.
• SQL As Understood By SQLiteSQLiteقواعد SQL التي يقبلها SQLite، عبارة تلو الأخرى، مع مخططات نحوية.
• Constraints — PostgreSQL DocumentationPostgreSQL Global Development Groupقيود التحقق وعدم القيمة الفارغة والتفرّد والمفتاح الأساسي والمفتاح الأجنبي والإقصاء — القواعد التي تفرضها قاعدة البيانات ويتحقق منها المختبِر.