SQL Joins Explained with Simple Examples

Joins are where SQL starts to feel powerful, and also where many developers start to feel lost. The good news is that you only need a handful of join types for nearly everything, and they all follow one idea: combine rows from two tables where a condition matches.

We'll use two small tables throughout:

users

id	name
1	Asha
2	Ravi
3	Meera

orders

id	user_id	total
10	1	40
11	1	25
12	2	60
13	9	15

Meera has no orders. Order 13 belongs to a user (9) who doesn't exist, perhaps a deleted account.

INNER JOIN: only matches

SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON o.user_id = u.id;

name	total
Asha	40
Asha	25
Ravi	60

Only rows that match on both sides. Meera disappears (no orders) and order 13 disappears (no user). JOIN on its own means INNER JOIN.

LEFT JOIN: everything from the left, plus matches

SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

name	total
Asha	40
Asha	25
Ravi	60
Meera	NULL

Every user appears. Where there's no matching order, the order columns are NULL. This is the join you'll use most after INNER, for things like "list all customers and their order counts, including those with zero."

RIGHT JOIN and FULL OUTER JOIN

• RIGHT JOIN is the mirror image: everything from the right table plus matches. In practice, people usually swap the table order and write a LEFT JOIN instead, which reads more naturally.
• FULL OUTER JOIN keeps everything from both sides, with NULLs where there's no match. Here it would include Meera and order 13. Useful for reconciling two data sets. (MySQL doesn't support it directly; you'd combine a LEFT and RIGHT join with UNION.)

The anti-join: "find rows with no match"

One of the most useful patterns: which users have never ordered?

SELECT u.name
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

Result: Meera. Or, often clearer:

SELECT u.name
FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

The same pattern finds orphaned records too: orders whose user no longer exists. (Foreign key constraints prevent those in the first place.)

Why your row count exploded

Joining a one-to-many relationship repeats the "one" side for each match. Asha appears twice above because she has two orders. That's correct, but it surprises people when they then SUM something from the users table.

Joining two one-to-many relationships at once (users → orders and users → addresses) multiplies rows: two orders × three addresses = six rows for Asha. Totals computed over that result are wrong.

Fixes:

• Aggregate before joining, using a subquery or CTE:

WITH order_totals AS (
  SELECT user_id, COUNT(*) AS orders, SUM(total) AS spent
  FROM orders
  GROUP BY user_id
)
SELECT u.name, COALESCE(t.orders, 0) AS orders, COALESCE(t.spent, 0) AS spent
FROM users u
LEFT JOIN order_totals t ON t.user_id = u.id;

• Or run separate queries for each relationship.

Join conditions vs WHERE conditions

With a LEFT JOIN, where you put a filter changes the result:

-- Keeps all users; only joins orders over 30
LEFT JOIN orders o ON o.user_id = u.id AND o.total > 30

-- Filters AFTER the join: users without big orders disappear,
-- effectively turning it back into an INNER JOIN
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.total > 30

This is one of the most common join bugs. Conditions about the joined table go in ON if you want to keep unmatched left rows.

Self joins

A table can join to itself, which is useful for hierarchies:

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

Performance tips

• Index the columns you join on, especially foreign keys. PostgreSQL doesn't index them automatically. (Indexing explained.)
• Select only the columns you need.
• If your app runs one query per row instead of a join, you have the N+1 problem.

FAQ

What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only rows that match in both tables. LEFT JOIN returns every row from the left table, with NULLs where the right table has no match.

How do I find records with no matching rows in another table?

Use a LEFT JOIN with WHERE right_table.id IS NULL, or a NOT EXISTS subquery. Both are called anti-joins.

Why does my join return duplicate rows?

In a one-to-many relationship, the "one" side repeats for every match on the "many" side. Aggregate in a subquery before joining, or select distinct values if that's what you need.