SQL Joins: INNER, LEFT, RIGHT & FULL with Examples

Start with the table grain before writing join behavior

A customer table joined to orders with LEFT JOIN keeps customers who never purchased. That matters when measuring conversion, while an INNER JOIN would silently remove them.

• INNER JOIN keeps matched keys from both tables; LEFT JOIN preserves every row from the left table even when a match is missing.
• RIGHT JOIN is the mirror of LEFT JOIN, while FULL OUTER JOIN is useful for reconciliation because unmatched rows from both sides remain visible.
• Duplicate join keys can multiply rows, so key cardinality should be checked before trusting aggregates.
• The correct join depends on the population you want to preserve, not on which syntax is shortest.

Work through a query with row-count checks

For SQL Joins, predict one visible result before each snippet runs—a row count, shape, statistic, ranking or error pattern—then compare it with the printed checkpoint.

-- SQL Joins · example 1
SELECT c.customer_id, c.city, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id;

SQL Joins checkpoint 1: Run the cell and inspect the printed values before changing anything.

-- SQL Joins · example 2
SELECT COUNT(*) AS rows, COUNT(DISTINCT customer_id) AS customers
FROM practice_orders;

SQL Joins checkpoint 2: rows | customers
30   | 12

-- SQL Joins · example 3
SELECT customer_id, COUNT(*) AS n
FROM practice_orders
GROUP BY customer_id
HAVING COUNT(*) > 1
ORDER BY n DESC;

SQL Joins checkpoint 3: Customers with more than one row are listed before any join or aggregation.

-- SQL Joins · example 4
SELECT region, COUNT(*) AS orders, ROUND(SUM(amount),2) AS revenue
FROM practice_orders
GROUP BY region
ORDER BY revenue DESC;

SQL Joins checkpoint 4: region | orders | revenue
West   | ...    | ...

-- SQL Joins · example 5
WITH x AS (
  SELECT amount FROM practice_orders WHERE amount IS NOT NULL
)
SELECT ROUND(SUM(amount),2) AS detail_total,
       ROUND((SELECT SUM(amount) FROM x),2) AS check_total
FROM x;

SQL Joins checkpoint 5: detail_total and check_total should match exactly.

Test duplicates, NULLs and unmatched keys

Self-check with answers

Practise with a reproducible SQLite-style dataset