SQL Joins Explained Simply (With Examples)

In a well-designed database, information is split across tables: customers in one, orders in another. A join puts them back together in a query — "show me each order with the customer's name". Joins are the part of SQL that confuses beginners most, so let's use one tiny example all the way through.

(New to SQL? Start with SQL for beginners.)

The example tables

customers

id	name
1	Ana
2	Ben
3	Chloe

orders

id	customer_id	total
10	1	25.00
11	1	40.00
12	2	15.00
13	99	60.00

Notice:

Ana has two orders, Ben has one, Chloe has none.
Order 13 belongs to customer 99, who doesn't exist (it shouldn't happen — a foreign key would prevent it — but it's useful for the example).

orders.customer_id points to customers.id. That's the link a join follows.

INNER JOIN: only matches

SELECT customers.name, orders.id AS order_id, orders.total
FROM orders
INNER JOIN customers ON customers.id = orders.customer_id;

name	order_id	total
Ana	10	25.00
Ana	11	40.00
Ben	12	15.00

An INNER JOIN returns only rows that have a match on both sides. Chloe (no orders) is missing. Order 13 (no customer) is missing. Plain JOIN means INNER JOIN.

LEFT JOIN: everything from the left, matches from the right

SELECT customers.name, orders.id AS order_id, orders.total
FROM customers
LEFT JOIN orders ON orders.customer_id = customers.id;

name	order_id	total
Ana	10	25.00
Ana	11	40.00
Ben	12	15.00
Chloe	NULL	NULL

A LEFT JOIN keeps every row from the first (left) table, and fills in the right table's columns where there's a match — or NULL where there isn't. Chloe appears, with empty order columns.

This is how you answer "customers with no orders":

SELECT customers.name
FROM customers
LEFT JOIN orders ON orders.customer_id = customers.id
WHERE orders.id IS NULL;

→ Chloe.

RIGHT JOIN and FULL JOIN

RIGHT JOIN is the mirror image: every row from the second table, matches from the first. You can always rewrite it as a LEFT JOIN by swapping the table order, which most people do.
FULL JOIN (FULL OUTER JOIN) keeps everything from both sides: Chloe and order 13, with NULLs where there's no match. Handy for finding mismatches between two tables. (MySQL doesn't support FULL JOIN directly; you combine a LEFT and RIGHT join with UNION.)

The Venn diagram, and its limits

You've probably seen joins drawn as overlapping circles: INNER is the overlap, LEFT is the whole left circle. It's a useful memory aid, but remember a join can multiply rows — Ana appears twice because she has two orders. Circles don't show that.

Joining more than two tables

Add a products table and an order_items table linking orders to products, and you chain joins:

SELECT customers.name, products.title, order_items.quantity
FROM orders
JOIN customers   ON customers.id = orders.customer_id
JOIN order_items ON order_items.order_id = orders.id
JOIN products    ON products.id = order_items.product_id;

Each JOIN ... ON follows one link. Short aliases make long queries readable:

SELECT c.name, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id;

Classic mistakes

1. Forgetting the ON condition

Without ON, or with the wrong columns, every row matches every row: 3 customers × 4 orders = 12 rows of nonsense. If a join returns far more rows than expected, check the ON.

2. Double-counting with SUM

Joining customers to orders and to support tickets, then summing order totals, counts each order once per ticket. Aggregate in separate queries or subqueries first.

3. A WHERE that cancels your LEFT JOIN

SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.total > 20;

Chloe disappears: her o.total is NULL, and NULL > 20 isn't true. If you want to keep all customers but only join big orders, put the condition in the ON:

LEFT JOIN orders o ON o.customer_id = c.id AND o.total > 20

4. Slow joins on big tables

Joins on columns without an index get slow as tables grow. Index foreign key columns like orders.customer_id. See database indexes.

5. Querying in a loop instead of joining

AI-generated code sometimes loads all orders, then queries the customer for each one — hundreds of queries instead of one join. That's the N+1 query problem.

The summary

A join combines rows from tables using a linking column.
INNER JOIN: only matches. LEFT JOIN: everything from the left, NULL where nothing matches.
RIGHT is a mirrored LEFT; FULL keeps everything from both.
Watch for multiplied rows, conditions in WHERE that undo a LEFT JOIN, and unindexed join columns.

EasySpawn servers come with PostgreSQL ready, so you — or Claude Code — can run real queries against real data and see the actual result, not a guess. See how it works or join the waitlist.

SQL Joins Explained Simply: INNER, LEFT, RIGHT, and FULL JOIN