S037 · Joins — INNER, LEFT, RIGHT, FULL, Anti-Join

"""joins_lab.py — every join flavour, plus row-multiplication demo."""
from __future__ import annotations
import sqlite3
 
conn = sqlite3.connect(":memory:")
cur = conn.cursor()
 
cur.executescript("""
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders (
    id           INTEGER PRIMARY KEY,
    customer_id  INTEGER,                       -- no FK on purpose (orphan demo)
    amount       INTEGER NOT NULL,
    order_date   TEXT    NOT NULL
);
CREATE TABLE order_items (
    order_id     INTEGER NOT NULL REFERENCES orders(id),
    product      TEXT NOT NULL,
    qty          INTEGER NOT NULL,
    unit_price   INTEGER NOT NULL
);
 
INSERT INTO customers VALUES (1,'Alice'),(2,'Bob'),(3,'Carol'),(4,'Dan');
INSERT INTO orders VALUES
    (100, 1, 500, '2026-01-10'),
    (101, 1, 300, '2026-02-05'),
    (102, 2, 800, '2026-01-20'),
    (103, 5, 200, '2026-03-01');     -- orphan customer_id=5
INSERT INTO order_items VALUES
    (100, 'Shirt', 2, 250),
    (100, 'Cap',   1, 300),          -- 2 items on order 100
    (101, 'Mug',   3, 100),          -- 1 item on order 101
    (102, 'Book',  4, 200);          -- 1 item on order 102
""")
 
def run(label: str, sql: str) -> None:
    print(f"\n-- {label} --")
    for row in cur.execute(sql):
        print(" ", row)
 
# ---- 1. INNER JOIN — matched only ----
run("INNER JOIN — 3 rows, no Carol/Dan/orphan", """
    SELECT c.name, o.amount FROM customers c
    JOIN orders o ON o.customer_id = c.id
    ORDER BY c.name, o.amount;
""")
 
# ---- 2. LEFT JOIN — every customer ----
run("LEFT JOIN — 5 rows, Carol/Dan appear with NULL amount", """
    SELECT c.name, o.amount FROM customers c
    LEFT JOIN orders o ON o.customer_id = c.id
    ORDER BY c.name, o.amount;
""")
 
# ---- 3. FULL OUTER — SQLite 3.39+ ----
try:
    run("FULL OUTER JOIN — orphan order 103 surfaces", """
        SELECT c.name, o.id, o.amount FROM customers c
        FULL OUTER JOIN orders o ON o.customer_id = c.id
        ORDER BY c.name NULLS LAST;
    """)
except sqlite3.OperationalError:
    run("FULL OUTER (older SQLite: simulate via UNION)", """
        SELECT c.name, o.amount FROM customers c
        LEFT JOIN orders o ON o.customer_id = c.id
        UNION ALL
        SELECT c.name, o.amount FROM orders o
        LEFT JOIN customers c ON c.id = o.customer_id
        WHERE c.id IS NULL;
    """)
 
# ---- 4. ANTI JOIN — two syntaxes ----
run("ANTI JOIN — customers who never ordered (NOT EXISTS)", """
    SELECT c.name FROM customers c
    WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
""")
 
run("ANTI JOIN — same result (LEFT JOIN + IS NULL)", """
    SELECT c.name FROM customers c
    LEFT JOIN orders o ON o.customer_id = c.id
    WHERE o.id IS NULL;
""")
 
# ---- 5. Orphan check — the OTHER direction ----
run("Data-quality: orders with a missing customer", """
    SELECT o.id, o.customer_id FROM orders o
    LEFT JOIN customers c ON c.id = o.customer_id
    WHERE c.id IS NULL;
""")
 
# ---- 6. Row multiplication demo ----
run("WRONG: order count after INNER JOIN with COUNT(*) — Carol/Dan missing", """
    SELECT c.name, COUNT(*) AS cnt FROM customers c
    JOIN orders o ON o.customer_id = c.id
    GROUP BY c.name;
""")
 
run("RIGHT: LEFT JOIN + COUNT(o.id) — zeros for Carol/Dan", """
    SELECT c.name, COUNT(o.id) AS cnt FROM customers c
    LEFT JOIN orders o ON o.customer_id = c.id
    GROUP BY c.name ORDER BY c.name;
""")
 
# ---- 7. The MULTIPLICATION BUG ----
run("BUG: SUM(amount) after 3-way join → order amounts get multiplied by item count", """
    SELECT c.name,
           SUM(o.amount) AS wrong_total,
           COUNT(*)      AS join_rows
    FROM customers c
    JOIN orders o       ON o.customer_id = c.id
    JOIN order_items oi ON oi.order_id   = o.id
    GROUP BY c.name;
""")
# Alice: (500+500+300)=1300  Bob: 800  — but 500 got counted TWICE because
# order 100 has 2 items. Multiplied by item count.
 
run("FIX: aggregate items FIRST in a subquery, then join at order grain", """
    SELECT c.name, SUM(o.amount) AS total
    FROM customers c
    JOIN orders o ON o.customer_id = c.id
    GROUP BY c.name ORDER BY c.name;
""")
 
# ---- 8. Cartesian mistake ----
run("DANGER: missing ON → Cartesian 4x4 = 16 rows", """
    SELECT c.name, o.id FROM customers c, orders o LIMIT 20;
""")
 
# ---- 9. EXPLAIN — see the plan ----
run("EXPLAIN plan for INNER JOIN with WHERE", """
    EXPLAIN QUERY PLAN
    SELECT c.name, o.amount FROM customers c
    JOIN orders o ON o.customer_id = c.id
    WHERE c.name = 'Alice';
""")
 
conn.close()