SQL Joins — All Types & Multi-Table

0. The Sample Tables

Joins need at least two related tables. We use customers ↔ transactions (from earlier), plus a new employees table for self joins.

1. INNER JOIN — Only Matching Rows

INNER JOIN returns only rows where there's a match in both tables. Rows without a match on either side are dropped.

-- Customers who have at least one transaction
SELECT c.customer_name, t.amount
FROM customers c
INNER JOIN transactions t
    ON c.customer_id = t.customer_id;

Customers 5 and 6 have no transactions, so they don't appear. Customer 1 appears three times (once per transaction).

2. LEFT JOIN — Keep All Left Rows

LEFT JOIN keeps all rows from the left (first) table, and matches from the right table where possible. Unmatched right columns become NULL.

-- ALL customers, with their transactions (NULL if none)
SELECT c.customer_name, t.amount
FROM customers c
LEFT JOIN transactions t
    ON c.customer_id = t.customer_id;

Now customers 5 and 6 appear with NULL amounts — because we kept the entire left table.

3. RIGHT JOIN and FULL OUTER JOIN

RIGHT JOIN — mirror of LEFT JOIN

Keeps all rows from the right table. Any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the tables.

-- These two are identical:
SELECT c.customer_name, t.amount
FROM customers c
RIGHT JOIN transactions t ON c.customer_id = t.customer_id;

SELECT c.customer_name, t.amount
FROM transactions t
LEFT JOIN customers c ON t.customer_id = c.customer_id;

FULL OUTER JOIN — keep everything from both sides

Returns all rows from both tables, matching where possible and filling with NULL where not.

SELECT c.customer_name, t.amount
FROM customers c
FULL OUTER JOIN transactions t
    ON c.customer_id = t.customer_id;

4. Finding Unmatched Rows (Anti-Join)

To find rows in one table with no match in another, use a LEFT JOIN and filter WHERE the right table's key IS NULL. This is one of the most-asked join questions.

-- Customers who have NEVER made a transaction
SELECT c.customer_name
FROM customers c
LEFT JOIN transactions t
    ON c.customer_id = t.customer_id
WHERE t.txn_id IS NULL;

Returns customers 5 and 6 — they were kept by the LEFT JOIN but found no transaction match.

5. Self Join and Cross Join

Self join — a table joined to itself

Used when rows relate to other rows in the same table — e.g., employees and their managers.

-- Each employee with their manager's name
SELECT e.emp_name AS employee, m.emp_name AS manager
FROM employees e
LEFT JOIN employees m
    ON e.manager_id = m.emp_id;

Using LEFT JOIN keeps the top boss (whose manager_id is NULL) in the result.

Cross join — the Cartesian product

CROSS JOIN pairs every row of the first table with every row of the second.

-- 3 sizes × 4 colors = 12 combinations
SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;

Useful for generating combinations. It also happens accidentally when you forget the ON condition.

6. The ON vs WHERE Trap (Critical)

For an outer join (LEFT/RIGHT), a filter on the right table behaves differently depending on whether you put it in ON or WHERE.

-- Filter in ON: still keeps ALL customers (shows txn only for one type)
SELECT c.customer_name, t.amount
FROM customers c
LEFT JOIN transactions t
    ON c.customer_id = t.customer_id
   AND t.amount > 1000;

Customers with no matching >1000 transaction still appear, with NULL amount.

-- Filter in WHERE: removes NULL rows → effectively an INNER JOIN
SELECT c.customer_name, t.amount
FROM customers c
LEFT JOIN transactions t
    ON c.customer_id = t.customer_id
WHERE t.amount > 1000;

Now customers with no >1000 transaction vanish — the WHERE kills the NULL rows the LEFT JOIN created.

7. Multi-Table Joins and JOIN vs UNION

Joining more than two tables

Chain joins one after another, each with its own ON. The result of the first join feeds the next.

SELECT e.emp_name, d.dept_name, l.city
FROM employees e
JOIN departments d ON e.dept_id = d.id
JOIN locations  l ON d.location_id = l.id;

JOIN vs UNION