SQL joins for beginners: a visual guide with UK sales data

Joins are where most people's SQL stops feeling tidy. Filtering, sorting and counting come quickly enough. Then someone asks a simple-sounding question — which customers in Leeds have never placed an order? — and suddenly three tables are staring back at you.

There are only four join shapes, and they are easier to picture than to memorise. What matters is being clear about two things: which table you want to keep, and what counts as a match. We will work through all four using a small sales dataset, so you can run every example yourself.

Two tables, one missing link

Our first table, customers, holds four people:

• 1 — Ayesha Khan, Manchester
• 2 — Tom Bradley, Bristol
• 3 — Priya Patel, Leeds
• 4 — Douglas Whyte, Glasgow

The second table, sales, holds the orders placed in March:

• 1001 — customer 1, 4 March, £249.00
• 1002 — customer 1, 11 March, £89.50
• 1003 — customer 2, 19 March, £412.75
• 1004 — customer 5, 22 March, £55.00

Two gaps stand out. Priya and Douglas have never ordered, so they appear in customers but not in sales. Order 1004 points at customer 5, a record that has since been deleted, so it appears in sales but not in customers. Those two kinds of gap are exactly what a join decides to keep or drop.

Inner join: only the rows that match

An inner join returns rows where the matching condition is true in both tables. Rows with no partner on either side are discarded.

SELECT c.customer_name, s.order_id, s.amount
FROM customers c
INNER JOIN sales s ON c.customer_id = s.customer_id;

You get three rows: Ayesha with orders 1001 and 1002, and Tom with 1003. Priya and Douglas vanish because nothing matches. Order 1004 also vanishes, because customer 5 cannot be found.

Two habits worth forming early. First, JOIN on its own means INNER JOIN — they are the same thing, so do not let the shorter form confuse you when reading someone else's query. Second, the ON clause is the matching rule, and it is doing all the work. Aliases such as c and s keep that rule readable once a query grows past two tables.

Left join: keep every row on the left

A left join keeps every row from the first table, matched or not, and fills in the gaps from the second table with NULL. Swap INNER for LEFT in the query above and you now get five rows. Ayesha and Tom look exactly as before; Priya and Douglas appear with a null order number and a null amount.

That null is not zero. It means "no matching row was found", which is why COUNT(s.order_id) would return 3 while COUNT(*) returns 5. Mixing the two up is one of the most common causes of wrong totals in reports.

Finding the rows that have no partner

The left join earns its keep when you filter for the nulls afterwards. This is often called an anti-join, and it answers the question from the start of the piece:

SELECT c.customer_name
FROM customers c
LEFT JOIN sales s ON c.customer_id = s.customer_id
WHERE s.order_id IS NULL;

Priya Patel and Douglas Whyte. No orders, no dates, nothing to sum — just two customers who have gone quiet.

Here is the trap. Adding a condition on the right-hand table in the WHERE clause, such as WHERE s.amount > 100, quietly turns your left join back into an inner join, because every null row fails the test. If you need to filter the right-hand table while keeping the unmatched rows, put the condition in the ON clause instead.

Right join: the mirror image

A right join keeps every row from the second table. Using the same two tables, you get four rows: the three matches plus order 1004, with a null customer name beside it.

In practice, most teams avoid right joins. It is usually clearer to swap the two tables around and write a left join, because readers tend to scan queries from the top and lose track of which side is being preserved. Learn to recognise right joins when you inherit older code, but reach for the left join when you are writing something new.

Full join: everything from both sides

A full outer join keeps all rows from both tables. Here you get six rows: the three matches, the two customers with no orders, and the one order with no customer.

This is the join for reconciliation work — checking a sales feed against an accounts system, comparing a CRM export with the billing database, or confirming that an overnight import landed every record it was supposed to. Anything where you want to see the mismatches rather than have them hidden.

Support varies, so know your database. PostgreSQL, SQL Server and Oracle offer FULL OUTER JOIN directly. MySQL does not, and you emulate it by joining one way, joining the other way, and combining the results with UNION — not UNION ALL, because the matched rows would otherwise appear twice. SQLite only gained right and full joins in version 3.39, so older installations need the same trick.

Which join should you reach for?

• Only rows present on both sides? Inner join.
• Everything from one table, plus whatever matches? Put that table first and use a left join.
• Rows in one table with no partner? Left join, then filter with IS NULL.
• A two-way reconciliation of mismatches? Full outer join.

The Venn diagrams you see everywhere are a useful shorthand, but they hide two things. A join is not really a set operation on whole tables — it is a test applied row by row — and it can produce more rows than either table if the condition matches several rows on each side. Join a customer table to an order table and two orders means two rows for that customer. Join it again to an addresses table with three entries per customer and you have six. If your totals suddenly balloon, duplicate keys are the first thing to check.

Practising with your own tables

Pick two tables you know well and count the rows in each one before you write any join. Then write the inner join, note the row count, and switch it to a left join. If the count does not change, every row already matched — which is useful to know, and easy to miss.

From there, run the anti-join pattern on a couple of key columns. It is the quickest way to find orphaned records, stale imports and lookup tables that no longer line up, and it takes about thirty seconds once you have the shape memorised. Add aliases, keep the join conditions beside the tables they belong to, and your queries will stay readable when a colleague opens them six months later.

One last note for anyone working with real customer data: sales tables usually contain personal information, so check that you are allowed to query it and follow your organisation's rules on handling and storing it. If you are unsure what applies to you, ask before you export anything.

Photo: Goumbik / Pixabay