SQL joins explained with examples: INNER, LEFT, RIGHT and FULL

In a well-designed database, data is split across several tables: customers in one place, orders in another. To answer questions such as «which customers ordered in March?» you need to put this information back together, and the tool for that is the join. Understanding how it works, row by row, avoids most of the mistakes people make when writing queries.

The sample tables

We will use two very small tables.

customers

orders

Note two deliberate quirks: Carla has no orders, and order 13 refers to a customer 9 who does not exist. In a database with foreign key constraints, order 13 could not exist, but it happens often with data imported from external sources.

INNER JOIN: matches only

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

The result contains only the pairs for which the condition is true:

Carla disappears because she has no orders, and order 13 disappears because it has no customer. Anna appears twice, once for each order. The word INNER is optional: writing just JOIN has the same effect.

LEFT JOIN: everything on the left

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

Every customer is present. Where there is no matching order, the columns from the other table are NULL. This is the join to use when the question is «all customers, with any orders they may have».

Finding what is missing

A very useful variant finds rows with no match:

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

It returns Carla: the customers who have never ordered. The same pattern, reversed, finds orphan orders like number 13, which is useful for checking data quality.

RIGHT JOIN and FULL OUTER JOIN

A RIGHT JOIN is the mirror image of a LEFT JOIN: it keeps every row from the right-hand table. In practice it is rarely used, because you can simply swap the order of the tables and write a LEFT JOIN, which is easier to read.

A FULL OUTER JOIN keeps every row from both tables:

It is useful for comparing two lists and seeing what exists on one side only, for example during a reconciliation between two systems. Not every database supports it: MySQL, for instance, has no FULL OUTER JOIN and requires you to combine two queries with UNION.

CROSS JOIN: every combination

A CROSS JOIN pairs every row of the first table with every row of the second, with no condition. With 3 customers and 4 orders it produces 12 rows. It is rarely needed on data tables, but it is handy for generating combinations, such as every size for every colour of a product. The risk is getting one by accident by forgetting the join condition: on large tables the result explodes.

The most common mistakes

Filtering in the wrong place

This query seems to ask for all customers with orders above 30:

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

But the condition in the WHERE clause discards rows where total is NULL, so Carla disappears and the LEFT JOIN behaves like an INNER JOIN. If you want every customer and only the orders above 30, the condition belongs in the ON clause:

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

Multiplied rows

If you join customers to both orders and support tickets, a customer with 3 orders and 2 tickets produces 6 rows. Summing totals in this situation gives an inflated result. The solution is to aggregate each table separately, in subqueries, and then join the already summarised results.

Conditions on the wrong columns

Joining tables on columns that look similar but do not correspond, such as an internal code and a supplier code, produces plausible but wrong results. It is always worth checking with a count: if the join returns more rows than you expect, there is something to understand.

In short

• INNER JOIN returns only rows with a match in both tables.
• LEFT JOIN keeps every row on the left, with NULL where there is no match.
• LEFT JOIN with WHERE ... IS NULL finds what is missing.
• Conditions on the right-hand table belong in ON, not WHERE, if you want to keep the LEFT JOIN.
• Several one-to-many joins in the same query multiply rows and distort sums.