SQL Joins Explained: INNER, LEFT, RIGHT and the One That Bites

Joins combine rows from two tables based on a condition. There are four you use, and two mistakes that account for most wrong results.

The four types#

-- Only rows that match in BOTH tables
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON o.user_id = u.id;
 
-- All users; NULL columns for those with no orders
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;
 
-- All orders; NULL for those with no matching user (rare in practice)
SELECT u.name, o.total
FROM users u
RIGHT JOIN orders o ON o.user_id = u.id;
 
-- Everything from both sides, NULLs where there is no match
SELECT u.name, o.total
FROM users u
FULL OUTER JOIN orders o ON o.user_id = u.id;

RIGHT JOIN is almost never used deliberately — it is a LEFT JOIN with the tables written in the wrong order. Reading a query where the "all rows" table is on the right is harder for no benefit.

Mistake 1: WHERE turns a LEFT JOIN into an INNER JOIN#

This is the single most common join bug:

-- Intent: all users, with their 2026 orders if any
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.created_at >= '2026-01-01';   -- ← silently drops users with no orders

The LEFT JOIN produces NULL for the order columns of users with no orders. Then WHERE o.created_at >= '2026-01-01' evaluates to NULL for those rows — which is not true — so they are filtered out. You have written an INNER JOIN with extra steps.

The fix is to put the condition in the ON clause, which filters before the join preserves unmatched rows:

SELECT u.name, o.total
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id
  AND o.created_at >= '2026-01-01';   -- ← correct

Rule of thumb: conditions on the left table belong in WHERE; conditions on the right table of a LEFT JOIN belong in ON.

The one legitimate use of the right table in WHERE is an anti-join:

-- Users who have never ordered
SELECT u.name
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

Mistake 2: row multiplication inflates aggregates#

A join is a Cartesian product filtered by the condition. If a user has 3 orders, joining produces 3 rows for that user — so anything you aggregate from the user side is counted 3 times.

-- WRONG: sums each user's credit once per order
SELECT SUM(u.credit), SUM(o.total)
FROM users u
JOIN orders o ON o.user_id = u.id;

It gets worse with two joins: a user with 3 orders and 2 addresses produces 6 rows.

Two correct approaches:

-- Aggregate before joining
SELECT u.name, u.credit, o.order_total
FROM users u
LEFT JOIN (
  SELECT user_id, SUM(total) AS order_total
  FROM orders GROUP BY user_id
) o ON o.user_id = u.id;
 
-- Or use DISTINCT inside the aggregate where the semantics allow
SELECT COUNT(DISTINCT o.id), COUNT(DISTINCT a.id)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
LEFT JOIN addresses a ON a.user_id = u.id;

Self joins and the NULL trap#

-- Employees with their manager's name
SELECT e.name, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

LEFT matters here: the CEO has no manager, and an INNER JOIN would drop them.

Note that NULL = NULL is not true in SQL — it is NULL. So rows never join on a NULL key, which is usually what you want but is worth knowing when a join mysteriously loses rows.

Frequently asked questions#

Is JOIN the same as INNER JOIN?#

Yes. JOIN defaults to INNER. Writing it explicitly makes the intent obvious to the next reader.

Are joins slower than subqueries?#

Usually not — query planners rewrite between the two forms. Write the clearer version and check the plan with EXPLAIN ANALYZE if it is slow.

How many joins is too many?#

There is no fixed number, but past five or six the planner's search space grows and plans get unstable. That is often a signal to denormalise a read path or use a materialised view.

Why does my LEFT JOIN return more rows than the left table?#

Because the right side matched multiple times. That is row multiplication, not a bug in the join — but it usually means you wanted an aggregate.

Related reading#

• SQL Query Optimization
• Format a long query with the SQL Formatter.

References#

• PostgreSQL docs: Joins Between Tables