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

JOIN תופר שתי טבלאות יחד

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

INNER JOIN הוא סוס העבודה. הוא מצמיד שורות משתי טבלאות בכל מקום שבו תנאי מתקיים, ומשמיט את כל השאר.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total REAL
);

INSERT INTO customers (id, name) VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Chen');
INSERT INTO orders (customer_id, total) VALUES (1, 49.99), (1, 12.50), (2, 80.00);

SELECT customers.name, orders.total
FROM customers
INNER JOIN orders ON orders.customer_id = customers.id;

שלושה לקוחות, שלוש הזמנות, אבל ל-Chen אין הזמנות, ולכן Chen לא מופיע. זה החלק ה"פנימי": רק שורות שנמצאה להן התאמה שורדות.

המודל המנטלי: מתאימים שורות, ואז מסננים

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

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);

INSERT INTO customers VALUES (1, 'Ada'), (2, 'Boris');
INSERT INTO orders VALUES (10, 1, 30), (11, 1, 70), (12, 2, 15);

SELECT c.name, o.id AS order_id, o.total
FROM customers AS c
INNER JOIN orders AS o ON o.customer_id = c.id;

כמה הרגלים שכדאי לאמץ כאן:

• תנו כינויים לטבלאות (customers AS c) כשתזכירו אותן יותר מפעם אחת. זה מפחית רעש.
• ציינו את הטבלה לפני העמודה (c.name, o.total) כשסביר ששתי הטבלאות מכילות אותה.
• הסדר ב-ON o.customer_id = c.id לא משנה: c.id = o.customer_id עובד אותו דבר.

INNER JOIN מול JOIN

ב-SQLite (וב-SQL התקני), JOIN לבדו פירושו INNER JOIN. מילת המפתח INNER היא אופציונלית.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);
INSERT INTO customers VALUES (1, 'Ada'), (2, 'Boris');
INSERT INTO orders VALUES (10, 1, 30), (11, 2, 15);

SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id;

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

ON מול USING

כשלעמודות החיבור יש אותו שם בשתי הטבלאות, USING (column) קצר יותר מ-ON a.col = b.col:

CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);

INSERT INTO customers VALUES (1, 'Ada'), (2, 'Boris');
INSERT INTO orders VALUES (10, 1, 30), (11, 2, 15);

SELECT name, total
FROM customers
INNER JOIN orders USING (customer_id);

USING (customer_id) עושה שני דברים: מתאים לפי customer_id שווה, וממזג את העמודה כך שהיא מופיעה פעם אחת בתוצאה. השתמשו בו כששני הצדדים באמת משתמשים באותו שם. הישארו עם ON כשהשמות שונים (orders.customer_id = customers.id) או כשהתנאי הוא יותר משוויון יחיד.

חיבור שלוש טבלאות

משרשרים JOIN על ידי הוספת פסוקיות JOIN ... ON .... כל אחת מקשרת את התוצאה המצטברת לטבלה נוספת.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);
CREATE TABLE order_items (
    order_id INTEGER,
    product TEXT,
    quantity INTEGER
);

INSERT INTO customers VALUES (1, 'Ada'), (2, 'Boris');
INSERT INTO orders VALUES (10, 1, 30), (11, 2, 15);
INSERT INTO order_items VALUES (10, 'Notebook', 2), (10, 'Pen', 5), (11, 'Mug', 1);

SELECT c.name, o.id AS order_id, i.product, i.quantity
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
INNER JOIN order_items i ON i.order_id = o.id
ORDER BY c.name, o.id;

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

סינון עם WHERE

ON אומר איך להצמיד שורות. WHERE מסנן את התוצאה המוצמדת. ב-INNER JOIN דווקא, תנאי נוסף ב-ON או ב-WHERE מייצר את אותן שורות, אבל המוסכמה היא לשמור את תנאי החיבור ב-ON ואת סינוני השורות ב-WHERE.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, country TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);

INSERT INTO customers VALUES (1, 'Ada', 'UK'), (2, 'Boris', 'FR'), (3, 'Chen', 'UK');
INSERT INTO orders VALUES (10, 1, 30), (11, 2, 80), (12, 3, 12);

SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
WHERE c.country = 'UK' AND o.total > 20;

זה נקרא כמו "חברו לקוחות והזמנות, ואז השאירו רק לקוחות מבריטניה שההזמנה שלהם מעל 20". שני תפקידים, שתי פסוקיות, והאני העתידי שלכם יודה לכם. (ברגע שמתחילים לכתוב LEFT JOIN, ההבחנה בין ON ל-WHERE מפסיקה להיות קוסמטית, אבל זה בעמוד הבא.)

כמה תנאים ב-ON

ON יכול להכיל כל ביטוי בוליאני, לא רק שוויון אחד. שימושי כשהקשר משתרע על יותר מעמודה אחת, או כשרוצים לסנן את הצד הימני בזמן החיבור.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL, status TEXT);

INSERT INTO customers VALUES (1, 'Ada'), (2, 'Boris');
INSERT INTO orders VALUES
    (10, 1, 30, 'paid'),
    (11, 1, 50, 'cancelled'),
    (12, 2, 15, 'paid');

SELECT c.name, o.id, o.total
FROM customers c
INNER JOIN orders o
    ON o.customer_id = c.id
   AND o.status = 'paid';

ההזמנה שבוטלה נעלמת כי התנאי השני נכשל. ב-INNER JOIN אפשר באותה מידה לכתוב WHERE o.status = 'paid' ולקבל את אותה תוצאה. גרסת ה-ON שומרת את הלוגיקה של "מה נחשב התאמה" קרוב ל-JOIN.

מלכודות נפוצות

כמה דברים שמכשילים אנשים:

• שכחה של פסוקית ה-ON. FROM a INNER JOIN b בלי ON היא שגיאת תחביר ב-SQLite. (פסיק לבד, FROM a, b, כן מתקמפל, נותן cross join, וכמעט אף פעם הוא לא מה שרציתם.)
• כפילויות לא צפויות. אם ללקוח יש שלוש הזמנות, השם שלו מופיע שלוש פעמים בתוצאה. זו התנהגות נכונה של JOIN, לא באג. בצעו צבירה עם GROUP BY אם אתם רוצים שורה אחת לכל לקוח.
• שורות חסרות. אם לקוח היה אמור להופיע ולא הופיע, תנאי החיבור לא התקיים: בדקו אם יש NULL בעמודות החיבור, או השתמשו ב-LEFT JOIN.
• שמות עמודות דו-משמעיים. SELECT id FROM customers JOIN orders ON ... נכשל כי לשתי הטבלאות יש id. ציינו את הטבלה: c.id או o.id.

הצעד הבא: LEFT JOIN

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