الـ self-join والـ anti-join

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

جدول مربوط بنفسه

في employees يحمل manager_id قيمة employee_id لصف آخر في الجدول نفسه. لعرض كل موظف بجانب اسم مديره، تربط employees بنفسه. الحيلة هي اسمان مستعاران، واحد لكل دور:

SELECT
  e.first_name || ' ' || e.last_name AS employee,
  e.title,
  m.first_name || ' ' || m.last_name AS manager
FROM employees AS e
LEFT JOIN employees AS m
  ON m.employee_id = e.manager_id
ORDER BY e.department, manager;

اقرأه كأنه نسختان من الجدول: e هو الموظف، وm هو من يتبع له. المعامل || يدمج النصوص. يُبقي الـ LEFT JOIN رانيا نفسها، وقيمة manager_id لديها NULL؛ أما الـ inner join فسيُسقط المديرة التنفيذية من الهيكل التنظيمي بصمت، وهذا ليس خطأً تودّ أن تشرحه لأحد.

يعمل النمط نفسه على customers.referred_by. من يجلب أكبر عدد من العملاء الجدد؟

SELECT
  r.customer_id,
  r.first_name || ' ' || r.last_name AS referrer,
  COUNT(*) AS customers_referred
FROM customers AS c
JOIN customers AS r ON r.customer_id = c.referred_by
GROUP BY r.customer_id
ORDER BY customers_referred DESC, r.customer_id
LIMIT 5;

هنا c هو العميل الجديد وr هو المُحيل. أحال ليو إبراهيم ثلاثة أشخاص؛ ولم يُحِل أحدٌ أكثر منه. تسمية الأسماء المستعارة حسب الأدوار (e/m، c/r) بدل a/b هي ما يُبقي الـ self-joins مقروءة.

الـ self-join يتقدّم مستوى واحدًا في كل مرة: من الموظف إلى المدير. أما «كل من في فريق بيتر، على أي عمق» فيحتاج إلى query تكراري، وستكتبه في القسم التالي.

الـ semi-join: هل يوجد تطابق؟

في الدرس الماضي وجد WHERE order_id IN (SELECT order_id FROM support_tickets) الطلبات التي لديها تذكرة واحدة على الأقل، دون الـ fan-out الذي يسبّبه الـ JOIN. هذا هو الـ semi-join. وEXISTS هي الصياغة الشائعة الأخرى:

SELECT c.customer_id, c.email
FROM customers AS c
WHERE EXISTS (
  SELECT 1 FROM orders AS o
  WHERE o.customer_id = c.customer_id
);

هذا الـ subquery مترابط (correlated): يشير إلى c من الـ query الخارجي، فيُقيَّم لكل عميل. كتابة SELECT 1 عُرف متّبع؛ فـ EXISTS لا يهتم إلا بعودة صف ما، لا بما فيه.

الـ anti-join: من ليس له تطابق؟

سؤال هدى عكس ذلك: العملاء الذين ليس لديهم طلبات. هناك طريقتان موثوقتان لكتابة الـ anti-join. الأولى هي NOT EXISTS:

SELECT
  strftime('%Y', c.signup_date) AS signup_year,
  COUNT(*) AS never_ordered
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1 FROM orders AS o
  WHERE o.customer_id = c.customer_id
)
GROUP BY signup_year;

85 عميلًا لم يطلبوا شيئًا قط، و52 منهم سجّلوا في 2024، أي قبل نهاية البيانات بسنة على الأقل. هذا هو الجزء الذي يمكن لهدى أن تتصرّف بناءً عليه: من سجّلوا في 2025 قد يتحوّلون إلى مشترين، أما من سجّلوا في 2024 فيحتاجون إلى حملة مختلفة.

والطريقة الثانية LEFT JOIN لا تُبقي منه إلا الصفوف غير المطابقة:

SELECT COUNT(*) AS never_ordered
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

العدد نفسه، 85. اختبر مفتاح الجدول الأيمن مقابل NULL، لأن المفتاح لا يكون NULL أبدًا في تطابق حقيقي. الصيغتان قياسيتان وسريعتان؛ ويفضّل معظم المحلّلين NOT EXISTS لأنه يصرّح بالنيّة ولا يمكن أن يسبّب fan-out.

الـ anti-join مع شروط

الـ anti-joins الحقيقية تحمل عادةً شرطًا: ليس «لم يطلب قط»، بل «لم يطلب مؤخرًا». يذهب الشرط داخل الـ subquery الخاص بـ NOT EXISTS، حيث يضيّق الطلبات التي تُعدّ تطابقًا. هؤلاء هم العملاء الذين اشتروا في 2024 ولم يشتروا شيئًا في 2025، وهي قائمة كلاسيكية بالعملاء المنقطعين:

SELECT COUNT(DISTINCT c.customer_id) AS lapsed_customers
FROM customers AS c
JOIN orders AS o24
  ON o24.customer_id = c.customer_id
 AND o24.order_date < '2025-01-01'
WHERE NOT EXISTS (
  SELECT 1 FROM orders AS o25
  WHERE o25.customer_id = c.customer_id
    AND o25.order_date >= '2025-01-01'
);

الربط بطلبات 2024 يسبّب fan-out (العميل الذي لديه خمسة طلبات في 2024 يظهر خمس مرات)، لذا يعدّ الـ query العملاء دون تكرار (DISTINCT). ووضع شرط 2025 في أي مكان خارج الـ subquery سيغيّر المعنى كليًا، وهو درس ON مقابل WHERE نفسه، لكن بمستوى أعمق.

أيّها تستخدم

بهذا تكتمل أدوات الـ JOIN. يكدّس القسم التالي هذه اللبنات في تحليلات أطول، بدءًا بطريقة لتسمية النتائج الوسيطة حتى يُقرأ query من 40 سطرًا من الأعلى إلى الأسفل.