SQLite Self Join: ربط الجدول بنفسه

الـ Self Join مجرد ربط مع Aliases

ما في شي مميز بخصوص الـ self join في SQLite. هو عبارة عن JOIN عادي، لكن صدف إن الطرفين هما نفس الجدول. الحيلة هنا إن SQLite يحتاج طريقة يميّز فيها بين النسختين، فبتعطي كل واحدة منهن alias مختلف.

بتلجأ لهالأسلوب لما يكون عندك صف في جدول يشير إلى صف آخر في نفس الجدول. المثال الكلاسيكي: جدول employees فيه كل صف يحتوي على manager_id يشير لموظف ثاني:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT * FROM employees;

آدا ليس لها مدير. بوريس وكليو يتبعان لآدا. دييغو وإيسمي يتبعان لبوريس. كل هذه العلاقات موجودة داخل جدول واحد فقط، وهنا بالضبط تظهر فائدة الـ self join.

الشكل الأساسي لـ self join

لكي نحصل على كل موظف مقترنًا باسم مديره، نربط جدول employees بنفسه. نسخة تلعب دور "الموظف"، والنسخة الأخرى تلعب دور "المدير":

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT
    e.name AS employee,
    m.name AS manager
FROM employees AS e
INNER JOIN employees AS m
    ON e.manager_id = m.id;

اقرأ الاستعلام كأنه جدولان مختلفان يتشاركان نفس التخزين. فـ e يمثّل صف الموظف، وm يمثّل صف المدير. وشرط الربط e.manager_id = m.id هو الذي يوازي بينهما: لكل موظف، ابحث في m عن الصف الذي يطابق id فيه قيمة manager_id للموظف.

لاحظ أن "آدا" لم تظهر في النتيجة. السبب أن قيمة manager_id لديها هي NULL، وINNER JOIN يتجاهل الصفوف التي لا تجد لها مطابقًا.

الإبقاء على الصفوف غير المطابقة باستخدام LEFT JOIN

إذا أردت أن تظهر جميع الأسماء في النتيجة، بما في ذلك من ليس لهم مدير، فاستبدل النوع بـ LEFT JOIN:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT
    e.name AS employee,
    m.name AS manager
FROM employees AS e
LEFT JOIN employees AS m
    ON e.manager_id = m.id
ORDER BY e.id;

الآن تظهر Ada مع قيمة NULL في عمود المدير. نفس آلية self join، لكن نوع الربط يقوم بما يفعله LEFT JOIN دائمًا: يحتفظ بكل صف من الجانب الأول، ويملأ الفراغات بقيم فارغة حين لا يوجد تطابق.

هذه هي الصيغة التي تحتاجها عادةً عند عرض قائمة بالأشخاص. فعبارة "بدون مدير" معلومة مهمة بحد ذاتها، وحذف الصف ليس خيارًا منطقيًا.

الـ Aliases ليست اختيارية

جرّب تنفيذ الـ join بدون aliases، وستجد أن SQLite لا يفهم ما تقصده:

SELECT name, manager_id FROM employees JOIN employees ON manager_id = id;
-- خطأ: اسم العمود غامض: name

كل عمود يظهر مرتين — مرة من كل نسخة من الجدول — وSQLite ما يقدر يحدد أي واحد تقصد. الحل هو استخدام alias في SQL، بحيث نعطي كل نسخة اسمًا خاصًا بها. اختر أسماء مستعارة تصف الدور الذي يلعبه الصف، لا اسم الجدول:

• e و m للموظف والمدير.
• parent و child للعلاقات الهرمية.
• a و b حين تقارن أزواجًا عشوائية.

هذا الـ alias هو السبب الرئيسي التي يخلي self join مقروءًا وواضحًا.

إيجاد الأزواج المتطابقة داخل نفس الجدول

الـ self join في SQLite ما يقتصر على البيانات الهرمية فقط. أي مرة تحتاج فيها تقارن صفوفًا داخل نفس الجدول، هذا النمط يناسبك. خذ مثلًا قائمة منتجات، ونبغى نطلع كل زوج من المنتجات التي لها نفس السعر:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL
);

INSERT INTO products (name, price) VALUES
    ('كوب',     9.99),
    ('دفتر', 9.99),
    ('قلم حبر',     2.50),
    ('قلم رصاص',  2.50),
    ('مصباح',   29.00);

SELECT a.name AS product_a, b.name AS product_b, a.price
FROM products AS a
INNER JOIN products AS b
    ON a.price = b.price
   AND a.id < b.id;

شيئان ينبغي ملاحظتهما هنا. الأول: a.price = b.price هو شرط المطابقة الفعلي. الثاني: a.id < b.id هو ما يمنع الاستعلام من إرجاع كل زوج مرتين (مرة كـ (Mug, Notebook) ومرة أخرى كـ (Notebook, Mug))، ويمنعه أيضًا من إقران كل صف مع نفسه. حيلة < هذه تستحق الحفظ — ستصادفها كلما احتجت إلى سرد أزواج من البيانات.

الصعود مستويين في التسلسل الهرمي

self join في SQLite يتعامل مع قفزة واحدة فقط في التسلسل الهرمي. لكن ماذا لو أردت معرفة "مدير المدير" لكل موظف؟ الحل: اربط الجدول بنفسه ثلاث مرات:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT
    e.name      AS employee,
    m.name      AS manager,
    gm.name     AS grand_manager
FROM employees AS e
LEFT JOIN employees AS m  ON e.manager_id = m.id
LEFT JOIN employees AS gm ON m.manager_id = gm.id
ORDER BY e.id;

كل alias جديد يعني صعود مستوى إضافي في الشجرة. هذي الطريقة تشتغل تمام لمستويين أو ثلاثة، لكنها تنهار بسرعة — لأنك تحتاج تعرف عمق التسلسل الهرمي وقت كتابة الاستعلام نفسه، وتضيف join لكل مستوى. وهذا بالضبط الجدار التي جاءت الـ recursive CTEs لكي تكسره.

متى لا تلجأ إلى self join؟

الـ self join هو الأداة المناسبة لما تحتاج أعمدة من طرفَي العلاقة في النتيجة النهائية. أما لو كنت تبغى فقط تصفّي البيانات — مثلاً تجيب كل موظف مديره هو "علي" — فإن استخدام subquery يكون عادةً أوضح وأسهل في القراءة:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Ada',   NULL),
    (2, 'Boris', 1),
    (3, 'Cleo',  1),
    (4, 'Diego', 2),
    (5, 'Esme',  2);

SELECT name
FROM employees
WHERE manager_id = (SELECT id FROM employees WHERE name = 'Ada');

لا داعي لألعاب الـ aliases، والمقصد واضح من أول نظرة. القاعدة الذهبية: هل تحتاج بيانات من كلا الصفّين في النتيجة؟ استخدم self join. هل تحتاج فقط قيمة تقارن بها؟ استخدم subquery.

أما حين تكون الهرمية بعمق غير محدّد (هياكل تنظيمية، أشجار ملفات، تعليقات متشعّبة)، فلا هذا الأسلوب ولا ذاك يصمد. هنا ندخل عالم recursive CTE.

التالي: الاستعلامات الفرعية (Subqueries)

self join و subqueries يتقاطعان في حلّ كثير من المسائل، ومعرفة أيّهما يناسب الموقف توفّر عليك ساعات من التحديق في كود SQL لاحقًا. الصفحة التالية تغوص في الاستعلامات الفرعية بأنواعها — scalar و correlated وصيغة IN — وتشرح متى يتألّق كلٌّ منها.