Week 3: Joins — INNER, LEFT, RIGHT, FULL & Self Joins

1. INNER JOIN

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

CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(id),
    total       NUMERIC(10, 2) NOT NULL
);

INSERT INTO customers (name) VALUES ('Asha'), ('Ravi'), ('Priya');
INSERT INTO orders (customer_id, total) VALUES (1, 49.99), (1, 12.50), (2, 99.00);
-- note: Priya (id 3) has no orders at all

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

--  name | total
-- ------+-------
--  Asha | 49.99
--  Asha | 12.50
--  Ravi | 99.00
-- (Priya doesn't appear — she has no matching row in orders)

INNER JOIN (often just written JOIN) returns only rows that have a match on both sides — Priya disappears entirely because there's no orders row with her customer_id. This is exactly correct behavior for "show me customers who've actually ordered something," and exactly wrong for "show me every customer, with orders if they have any."

2. LEFT JOIN & RIGHT JOIN

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

--  name  | total
-- -------+-------
--  Asha  | 49.99
--  Asha  | 12.50
--  Ravi  | 99.00
--  Priya | NULL   -- kept, with NULL where there was no match

LEFT JOIN keeps every row from the table listed first (the "left" table), filling in NULL for columns from the right table wherever there's no match. RIGHT JOIN is the mirror image — every row from the table listed second — and in practice is rarely used, since swapping the table order and writing LEFT JOIN instead expresses the same query more conventionally.

SELECT customers.name
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
WHERE orders.id IS NULL; -- only rows where the LEFT JOIN found nothing to match

3. FULL OUTER JOIN & Self Joins

FULL OUTER JOIN keeps every row from both tables, matched or not — rarer in practice than LEFT JOIN, but the right tool for "show me everything from both sides, and where they don't line up."

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

-- who reports to whom?
SELECT emp.name AS employee, mgr.name AS manager
FROM employees emp
LEFT JOIN employees mgr ON emp.manager_id = mgr.id;

A self join is just a normal join where a table is joined to itself, using two different aliases (emp and mgr above) so SQL can tell the two "copies" apart — the only genuinely new idea here is the aliasing, not the join mechanics.

4. Joining Three or More Tables

CREATE TABLE order_items (
    id       SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(id),
    product  TEXT NOT NULL,
    quantity INT NOT NULL
);

SELECT customers.name, orders.id AS order_id, order_items.product, order_items.quantity
FROM customers
JOIN orders ON customers.id = orders.customer_id
JOIN order_items ON orders.id = order_items.order_id
WHERE customers.name = 'Asha';

Each additional JOIN narrows or extends the row set one step at a time — read it top to bottom as "start with customers, bring in their orders, then bring in each order's items." A common mistake once queries get this deep is an accidental many-to-many fan-out (a customer with 3 orders, each with 4 items, produces 12 rows) — not a bug, just something to account for when aggregating on top of a multi-table join.

5. Hands-on Exercise

6. Knowledge Check

Four quick questions. Expand each to check your answer.