SQL JOINs Explained with Simple Examples

Relational databases split data into separate tables to avoid repetition: customers in one table, orders in another. JOINs are how you put that data back together when you query it.

The best way to understand joins is to watch them work on real rows. We'll use two tiny tables throughout this guide.

Our example tables#

customers

orders

Notice two things: Linus has no orders, and order 104 belongs to customer 4, who doesn't exist (perhaps the account was deleted). These edge cases are exactly where the different joins behave differently.

Want to follow along? Here's the setup SQL, which works in SQLite, PostgreSQL and MySQL:

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total DECIMAL(10, 2));

INSERT INTO customers VALUES (1, 'Ada'), (2, 'Grace'), (3, 'Linus');
INSERT INTO orders VALUES (101, 1, 49.00), (102, 1, 15.50), (103, 2, 99.99), (104, 4, 25.00);

Databases don't guarantee row order without ORDER BY, so the results below are sorted for readability.

INNER JOIN: only matching rows#

An INNER JOIN returns rows where the join condition matches in both tables:

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

Linus disappears because he has no orders, and so does order 104 because it has no customer. Writing JOIN on its own means INNER JOIN.

LEFT JOIN: everything from the left table#

A LEFT JOIN keeps every row from the left table (the one after FROM) and fills in NULL where there's no match on the right:

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

Linus is back, with NULLs in the order columns. After INNER JOIN, this is the join you'll use most often.

Finding rows with no match#

A classic trick is to combine LEFT JOIN with WHERE ... IS NULL to find "orphans". Here we find customers who have never ordered:

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

Result: Linus.

RIGHT JOIN: everything from the right table#

A RIGHT JOIN is the mirror image: it keeps every row from the right table.

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

Now the orphaned order 104 appears with no customer name. In practice, most developers rewrite right joins as left joins by swapping the table order. A query is easier to read when the "keep everything" table always comes first.

FULL OUTER JOIN: everything from both#

A FULL OUTER JOIN keeps all rows from both tables, matching them where it can:

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

It's handy for reconciling data: comparing two lists and spotting what's missing on either side.

Summary table#

Aggregating after a join#

Joins become really powerful with GROUP BY. Here's each customer's order count and total spend, including customers with zero orders:

SELECT
  c.name,
  COUNT(o.id)               AS order_count,
  COALESCE(SUM(o.total), 0) AS total_spent
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY total_spent DESC;

Common mistakes#

• Filtering the right table in WHERE after a LEFT JOIN. A condition like WHERE o.total > 20 silently removes the NULL rows and turns your LEFT JOIN into an INNER JOIN. Move the condition into the ON clause instead: LEFT JOIN orders AS o ON o.customer_id = c.id AND o.total > 20.
• Using the wrong join condition. Joining on the wrong columns, or with no real condition at all, can produce a Cartesian product: every row paired with every other row.
• Joining on columns with different types. This can stop the database from using its indexes, which makes queries slow.

Recap#

• INNER JOIN returns only rows that match on both sides.
• LEFT JOIN returns all rows from the left table, with NULLs where there's no match.
• RIGHT JOIN is the mirror of LEFT and is usually rewritten as a LEFT JOIN.
• FULL OUTER JOIN returns everything from both tables.
• LEFT JOIN ... WHERE right.id IS NULL finds rows without a match.

Set up the two tables above in SQLite (sqlite3 shop.db) and run each query yourself. Five minutes of experimenting beats an hour of reading.