Joining Tables: INNER, LEFT & More
Combine tables by following their keys: why data is split across tables, INNER JOIN, LEFT JOIN, finding things with no match, joining three or four tables, self-joins, and the fan-out trap that silently inflates totals.
Why data is split into tables
An order needs the customer's name and city. Why not store them in the orders table? Because a customer with 5 orders would have their details copied 5 times, and when they move city, you would have to update 5 rows (and forget one). So each fact is stored once, and tables refer to each other by keys: orders.customer_id holds the customer_id of the customer who ordered. A join follows those keys to put the information back together.
INNER JOIN: only rows that match
JOIN … ONsays which table to add and how rows match.JOINon its own meansINNER JOIN.oandcare table aliases: short names that save typing. Use them to say which table a column comes from (c.name), which is required when both tables have a column with that name.- An inner join keeps only pairs that match. A customer with no orders does not appear at all.
Watch joins happen
See which rows an INNER and a LEFT join keep, and how a key that appears several times multiplies rows.
Interactive: this part runs in your browser.
LEFT JOIN: keep everything from the left
A LEFT JOIN keeps every row from the first (left) table, and fills the right table's columns with NULL when there is no match. Use it when the left table is the list you care about, like "all customers, with their orders if they have any":
Note COUNT(o.order_id), not COUNT(*): for a customer with no orders, the joined row has NULL there, so the count is 0 instead of 1.
Finding missing matches (anti-join)
"Which customers have never ordered?" is a LEFT JOIN, then keep the rows where the right side is NULL:
The same question can be written with NOT EXISTS (next lesson). Questions like "products never sold" and "patients with no follow-up" all have this shape.
Joining several tables
Add one JOIN per table, each with its own ON. To get "who bought what", go from orders to customers, and from order_items to products:
Draw the keys as arrows on paper first: every arrow is one ON condition.
Other joins
- RIGHT JOIN: the mirror of LEFT (keep everything from the right). Rarely used: swap the tables and use LEFT.
- FULL OUTER JOIN: keep unmatched rows from both sides (SQLite supports it since version 3.39).
- CROSS JOIN: every row with every row (3 × 4 = 12 rows), useful for building grids such as every product × every month.
- Self-join: a table joined to itself with two aliases, for example customers in the same city:
FROM customers a JOIN customers b ON a.city = b.city AND a.customer_id < b.customer_id.
The fan-out trap
When you join a table where one row matches many (one order has many items), the "one" side is repeated. If you then add up a column from the "one" side, it is counted several times. Example: summing a delivery fee stored on orders after joining order_items counts the fee once per item. The fix is to aggregate the "many" table first (in a subquery or CTE), then join. Always check the row count before and after a join: if it grew unexpectedly, you have fan-out.
Try it: join the shop tables
Compare the INNER and LEFT results, then look closely at the counts.
Interactive: this part runs in your browser.
Practice: check yourself
Write the query, then press ✔ Check my answer: your query runs on a fresh copy of the shop database and its result is compared with the correct one. ▶ Run just shows your result. Each run starts from the original data, so you cannot break anything.
Tables: customers(customer_id, name, city, country, joined) · products(product_id, name, category, price) · orders(order_id, customer_id, order_date, status) · order_items(order_id, product_id, quantity, unit_price)
Show each order's order_id, order_date and the customer's name.
Show the name of every customer who has never placed an order.
Show every customer's name and their number of orders (orders), including customers with 0.
Show each product category and the total units sold (sum of quantity in order_items).
Summary
Key words
- join
- combining rows of two tables that match on a condition
- primary key
- a column that uniquely identifies each row (customer_id in customers)
- foreign key
- a column that holds another table's key (customer_id in orders)
- INNER JOIN
- only rows with a match on both sides
- LEFT JOIN
- every row from the left; NULLs where the right has no match
- anti-join
- LEFT JOIN … WHERE right.key IS NULL: rows with no match
- table alias
- a short name: FROM orders AS o
- fan-out
- rows multiplied by a one-to-many join
Common mistakes
- Forgetting the ON condition (or joining on the wrong columns), which pairs every row with every row.
- Ambiguous column names: write
c.customer_id, notcustomer_id, when both tables have it. - Putting a filter on the right table of a LEFT JOIN in WHERE, which silently turns it into an inner join. Put it in the ON clause instead.
- COUNT(*) after a LEFT JOIN counts the NULL row as 1: count a column from the right table.
- Summing after a one-to-many join (fan-out).
Quiz
1. Which join lists every customer, including those with no orders?
2. How do you find customers who have never ordered?
3. Why use COUNT(o.order_id) instead of COUNT(*) after a LEFT JOIN?
4. Joining orders (60 rows) to order_items (150 rows) gives about how many rows?
5. In FROM orders AS o, what is o?
📚 Further reading
Trusted, mostly free sources: official documentation, well-known textbooks and courses. When you want to confirm or go deeper on anything here, start with these.
- PostgreSQL Tutorial (official documentation) ↗ SQL concepts, joins, aggregates, transactions and window functions
- SQLBolt ↗ free interactive SQL lessons in the browser