SQLite LEFT JOIN: שמירה על כל השורות מהטבלה השמאלית

LEFT JOIN שומר את כל מה שבצד השמאלי

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

LEFT JOIN מחזיר כל שורה מהטבלה השמאלית. אם בטבלה הימנית יש שורה תואמת, מקבלים את העמודות התואמות. אם לא, עדיין מקבלים את השורה השמאלית, והעמודות של הצד הימני חוזרות כ-NULL.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    total REAL
);

INSERT INTO users (id, name) VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders (user_id, total) VALUES (1, 49.99), (1, 12.50), (2, 99.00);

SELECT u.name, o.total
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id;

ל-Cleo אין הזמנות, אבל היא עדיין מופיעה, עם NULL בעמודה total. החליפו את LEFT JOIN ב-INNER JOIN ו-Cleo נעלמת לגמרי.

המודל המנטלי

קראו את השאילתה מלמעלה למטה וחשבו על הטבלה השמאלית כעוגן. כל שורה ב-users תופיע בפלט, לא משנה מה. ה-LEFT JOIN שואל אז, עבור כל משתמש: "האם יש שורה תואמת ב-orders?"

• נמצאה התאמה → מדביקים את העמודות התואמות לשורת המשתמש.
• כמה התאמות → נוצרת שורת פלט אחת לכל התאמה (ל-Ada יש שתי הזמנות, ולכן היא מופיעה פעמיים).
• אין התאמה → נוצרת שורה אחת עם NULL בכל עמודה מהטבלה הימנית.

המקרה האחרון הוא כל הסיבה לקיומו של LEFT JOIN. NULL כאן לא אומר "אנחנו לא יודעים": הוא אומר "אין שום דבר בצד הימני להדביק".

LEFT OUTER JOIN היא אותה פעולה. מילת המפתח OUTER אופציונלית ב-SQLite, ורוב האנשים משמיטים אותה.

מציאת שורות ללא התאמה

שימוש קלאסי ב-LEFT JOIN: למצוא שורות בטבלה השמאלית שאין להן התאמה בצד הימני. הטריק הוא לסנן לפי עמודה מהטבלה הימנית שהיא NOT NULL בנתונים עצמם, בדרך כלל המפתח הראשי שלה, ולבדוק אם היא NULL אחרי החיבור:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, total REAL);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (1, 1, 49.99), (2, 2, 99.00);

SELECT u.name
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
WHERE o.id IS NULL;

רק Cleo חוזרת. ה-JOIN מצרף נתוני הזמנות במקום שבו הם קיימים; ה-WHERE o.id IS NULL משאיר אז רק את השורות שבהן הצירוף נכשל. לפעמים קוראים לזה "anti-join".

ON מול WHERE: המלכודת העדינה

זה הבאג הנפוץ ביותר עם LEFT JOIN, ושווה לעצור עליו. תנאים נכנסים לפסוקית ה-ON או לפסוקית ה-WHERE, אבל ב-outer join הם מתנהגים שונה מאוד.

• ON רץ בזמן החיבור. התנאים שם קובעים אילו שורות מהצד הימני נחשבות להתאמה.
• WHERE רץ אחרי שהחיבור הפיק את השורות שלו. הוא מסנן את התוצאה המשולבת.

ראו מה קורה כששמים תנאי על הטבלה הימנית ב-WHERE:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, status TEXT);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (1, 1, 'shipped'), (2, 2, 'pending');

-- שגוי: הופך בשקט את ה-LEFT JOIN ל-INNER JOIN
SELECT u.name, o.status
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
WHERE o.status = 'shipped';

ל-Cleo אין הזמנה, ולכן o.status הוא NULL בשורה שלה, ו-NULL = 'shipped' לא מתקיים: היא מסוננת החוצה. הסטטוס של Boris הוא 'pending', וגם הוא נשמט. ה-LEFT JOIN התנהג בשקט כמו INNER JOIN.

הפתרון: להעביר את התנאי ל-ON, כך שהוא יסנן התאמות ולא שורות פלט:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, status TEXT);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (1, 1, 'shipped'), (2, 2, 'pending');

SELECT u.name, o.status
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id AND o.status = 'shipped';

עכשיו כל משתמש מופיע. Ada מקבלת את ההזמנה שנשלחה; Boris מקבל NULL (ההזמנה הממתינה שלו לא נחשבה להתאמה); Cleo מקבלת NULL (אין לה הזמנות בכלל). זו התשובה הנכונה כשהשאלה היא "הראו לי כל משתמש, ואת ההזמנות שנשלחו שלו אם יש".

כלל אצבע: תנאים על הטבלה השמאלית יכולים להיכנס ל-WHERE. תנאים על הטבלה הימנית שייכים כמעט תמיד ל-ON, אלא אם אתם רוצים במפורש למצוא שורות ללא התאמה עם IS NULL.

ספירה עם LEFT JOIN

משימה נפוצה: לספור שורות קשורות לכל אב, כולל אבות עם אפס. INNER JOIN היה משמיט את האפסים. LEFT JOIN יחד עם COUNT של עמודה מהצד הימני נותן את התשובה הנכונה:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, total REAL);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (1, 1, 49.99), (2, 1, 12.50), (3, 2, 99.00);

SELECT u.name, COUNT(o.id) AS order_count, COALESCE(SUM(o.total), 0) AS revenue
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
GROUP BY u.id, u.name
ORDER BY u.name;

שני דברים ששווה לשים לב אליהם:

• COUNT(o.id) סופר שורות מהצד הימני שאינן NULL. Cleo מקבלת 0, לא 1, כי COUNT מתעלם מ-NULL. אם הייתם כותבים COUNT(*), Cleo הייתה מקבלת 1 (השורה קיימת, פשוט יש בה NULL). כמעט תמיד, COUNT(right.id) הוא מה שאתם רוצים.
• COALESCE(SUM(o.total), 0) הופך את הסכום NULL של Cleo ל-0. בלי זה, היא הייתה מופיעה עם הכנסות NULL, מה שנכון טכנית אבל מכוער בתצוגה.

חיבור כמה טבלאות

LEFT JOIN ניתן לשרשור. כל JOIN לוקח את התוצאה המצטברת ומחבר אליה טבלה נוספת. ברגע שהפכתם עמודה לכזו שיכולה להיות NULL בעזרת LEFT JOIN, המשיכו להשתמש ב-LEFT JOIN לכל טבלה שתלויה בה, אחרת ה-INNER JOIN הבא ישמיט בשקט שורות שרציתם לשמור.

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, total REAL);
CREATE TABLE shipments (order_id INTEGER PRIMARY KEY, carrier TEXT);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (10, 1, 49.99), (11, 2, 99.00);
INSERT INTO shipments VALUES (10, 'UPS');

SELECT u.name, o.total, s.carrier
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
LEFT JOIN shipments AS s ON s.order_id = o.id;

שלושה משתמשים חוזרים. ל-Ada יש הזמנה ומשלוח. ל-Boris יש הזמנה אבל אין משלוח (carrier הוא NULL). ל-Cleo אין הזמנה, ולכן גם o.total וגם s.carrier הם NULL. שרשרת ה-LEFT JOIN שומרת על כל משתמש, לא משנה באיזה שלב בשרשרת הקשרים הנתונים נגמרים.

מתי LEFT JOIN הוא הבחירה הנכונה

השתמשו ב-LEFT JOIN כשהשאלה עוסקת ביסודה בטבלה השמאלית, והטבלה הימנית היא מידע משלים. ניסוחים כמו "כל משתמש, עם ההזמנות שלו אם יש" או "כל המוצרים והביקורת האחרונה שלהם" מתורגמים ישירות ל-LEFT JOIN.

השתמשו ב-INNER JOIN כששני הצדדים נדרשים באותה מידה: "הזמנות עם פרטי המשתמש שלהן" לא הגיוני להזמנה בלי משתמש, ולכן הסינון של INNER JOIN הוא מה שאתם רוצים.

אם אתם מוצאים את עצמכם כותבים LEFT JOIN ... WHERE right.col IS NOT NULL, רציתם INNER JOIN. אם אתם מוצאים את עצמכם כותבים LEFT JOIN ... WHERE right.col IS NULL, רציתם anti-join, ועשיתם את זה נכון.

הצעד הבא: Self-Join

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