Self Join ב-SQLite: שאילתה של טבלה מול עצמה

Self join הוא פשוט JOIN עם כינויים

אין שום דבר מיוחד ב-self join. זה JOIN רגיל ששני הצדדים שלו הם במקרה אותה טבלה. הטריק הוא ש-SQLite צריכה דרך להבחין בין שני העותקים, ולכן נותנים לכל אחד כינוי.

משתמשים בו בכל פעם ששורה בטבלה מפנה לשורה אחרת באותה טבלה. המקרה הקלאסי: טבלת 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;

ל-Ada אין מנהל. Boris ו-Cleo כפופים ל-Ada. Diego ו-Esme כפופים ל-Boris. היחס חי כולו בתוך טבלה אחת, ובדיוק שם 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 היא שורת המנהל. תנאי ה-JOIN e.manager_id = m.id מיישר אותן: לכל עובד, מצאו את השורה ב-m שה-id שלה מתאים ל-manager_id של העובד.

שימו לב ש-Ada לא הופיעה. ה-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, רק שסוג ה-JOIN עושה את מה ש-LEFT JOIN תמיד עושה: שומר כל שורה מהטבלה הראשונה (הצד השמאלי של ה-JOIN) וממלא ריקים כשאין התאמה.

זו הצורה שבדרך כלל רוצים כשמציגים רשימת אנשים. "אין מנהל" הוא מידע, ולהשמיט את השורה זה לא.

הכינויים אינם אופציונליים

נסו את ה-JOIN בלי כינויים, ול-SQLite אין מושג למה התכוונתם:

SELECT name, manager_id FROM employees JOIN employees ON manager_id = id;
-- Error: ambiguous column name: name

כל עמודה מופיעה פעמיים, פעם מכל עותק של הטבלה, ו-SQLite לא יכולה לבחור. כינויים פותרים את זה כשהם נותנים לכל מופע שם משלו. בחרו כינויים שמתארים את התפקיד שהשורה ממלאת, לא את הטבלה:

• e ו-m לעובד ולמנהל.
• parent ו-child להיררכיות.
• a ו-b כשמשווים זוגות שרירותיים.

הכינוי הוא כל הסיבה ש-self join נקרא בצורה נקייה.

מציאת זוגות בתוך טבלה

Self join לא נועד רק להיררכיות. בכל פעם שרוצים להשוות שורות באותה טבלה, הדפוס מתאים. הנה רשימת מוצרים, ואנחנו רוצים כל זוג שהמחיר שלו זהה:

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

INSERT INTO products (name, price) VALUES
    ('Mug',     9.99),
    ('Notebook', 9.99),
    ('Pen',     2.50),
    ('Pencil',  2.50),
    ('Lamp',   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 מטפל בקפיצה אחת בהיררכיה. רוצים את המנהל של המנהל של כל עובד? חברו שלוש פעמים:

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;

כל כינוי חדש מייצג עוד רמה אחת למעלה בעץ. זה עובד היטב לשתיים או שלוש קפיצות, אבל מתפרק מהר: צריך לדעת את עומק ההיררכיה בזמן כתיבת השאילתה, ולהוסיף JOIN לכל רמה. זה הקיר ש-CTE רקורסיבי הומצא כדי לפרוץ.

מתי לא להשתמש ב-self join

Self join הוא הכלי הנכון כשצריך בתוצאה עמודות משני הצדדים של היחס. אם צריך רק לסנן, למשל למצוא כל עובד שהמנהל שלו הוא Ada, תת-שאילתה נקראת לעתים קרובות טוב יותר:

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');

בלי התעמלות של כינויים, והכוונה ברורה לגמרי. כלל האצבע: אתם רוצים נתונים משתי השורות בפלט? Self join. אתם רק צריכים ערך להשוות אליו? תת-שאילתה.

להיררכיות בעומק שרירותי (תרשימים ארגוניים, עצי קבצים, תגובות בשרשור) אף אחד מהדפוסים לא מתאים לגדילה. זה התחום של CTE רקורסיבי.

הבא: תת-שאילתות

Self join ותת-שאילתות פותרים בעיות חופפות, ולדעת איזה מהם מתאים חוסך הרבה פזילה ל-SQL בהמשך. העמוד הבא מעמיק בתת-שאילתות, בצורות הסקלרית, המתואמת וה-IN, ובמקום שבו כל אחת מהן מצטיינת.