SQL & Databases
Topic 7 · Querying data · ⏱ about 3 h

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.

Show:
In this topic
Beginner

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.

Beginner

INNER JOIN: only rows that match

SELECT o.order_id, o.order_date, c.name, c.city FROM orders AS o JOIN customers AS c ON c.customer_id = o.customer_id;
  • JOIN … ON says which table to add and how rows match. JOIN on its own means INNER JOIN.
  • o and c are 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.
Try it

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.

Beginner

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":

SELECT c.name, COUNT(o.order_id) AS orders FROM customers AS c LEFT JOIN orders AS o ON o.customer_id = c.customer_id GROUP BY c.customer_id, c.name ORDER BY orders;

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.

Beginner

Finding missing matches (anti-join)

"Which customers have never ordered?" is a LEFT JOIN, then keep the rows where the right side is NULL:

SELECT c.name FROM customers AS c LEFT JOIN orders AS o ON o.customer_id = c.customer_id WHERE o.order_id 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.

Beginner

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:

SELECT c.name, p.name AS product, i.quantity, i.unit_price * i.quantity AS line_total FROM orders AS o JOIN customers AS c ON c.customer_id = o.customer_id JOIN order_items AS i ON i.order_id = o.order_id JOIN products AS p ON p.product_id = i.product_id WHERE o.status = 'delivered';

Draw the keys as arrows on paper first: every arrow is one ON condition.

Beginner

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.
Beginner

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

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.

Beginner

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)

Exercise 1. Who placed each order

Show each order's order_id, order_date and the customer's name.

Exercise 2. Customers who never ordered

Show the name of every customer who has never placed an order.

Exercise 3. Orders per customer, including zero

Show every customer's name and their number of orders (orders), including customers with 0.

Exercise 4. Units sold per category

Show each product category and the total units sold (sum of quantity in order_items).

Beginner

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, not customer_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).
Check yourself

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?

Go deeper

📚 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.

Ready