SQL joins explained: INNER, LEFT and finding rows with no match

Relational databases split data into tables so nothing is stored twice. A customer’s name lives in customers, and each order only stores customer_id. A join puts the pieces back together at query time.

The examples use four customers (Ada, Brian, Chen, Dana) and five orders. Dana has no orders.

INNER JOIN: only rows that match

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

This returns five rows: two for Ada, one for Brian, two for Chen. Dana does not appear because no order matches her id. JOIN on its own means INNER JOIN.

A join can multiply rows. Ada shows up twice because she has two orders. If you sum a customer column after a join, you count it once per matching order. Aggregate before joining, or count distinct ids, when that matters.

LEFT JOIN: keep everything from the left table

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

This returns six rows. Dana now appears once, with NULL in every column that comes from orders. RIGHT JOIN is the mirror image. Most people write every outer join as a LEFT JOIN with the tables swapped, because that is easier to read.

FULL OUTER JOIN keeps unmatched rows from both sides. PostgreSQL supports it, and so does SQLite since version 3.39. MySQL does not; there you combine a LEFT JOIN and a RIGHT JOIN with UNION.

Finding rows with no match

To find customers who have never ordered, use a LEFT JOIN and keep only the rows where the right side is missing:

SELECT c.name
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.id IS NULL;          -- test a column that is never NULL in a real match

NOT EXISTS gives the same result and often states the intent more clearly:

SELECT c.name
FROM customers AS c
WHERE NOT EXISTS (
  SELECT 1 FROM orders AS o WHERE o.customer_id = c.id
);

Be careful with NOT IN (SELECT customer_id FROM orders). If the subquery returns even one NULL, the whole condition becomes unknown and the query returns no rows at all. NOT EXISTS does not have this problem.

ON versus WHERE in a LEFT JOIN

A filter on the right table changes meaning depending on where you put it:

-- Filter in WHERE: customers without a paid order disappear (acts like INNER JOIN)
SELECT c.name, o.id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.status = 'paid';

-- Filter in ON: every customer stays, only paid orders are attached
SELECT c.name, o.id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id AND o.status = 'paid';

On the sample data, the first query returns Ada and Chen only. The second also returns Brian and Dana, with an empty order id.

Counting after a LEFT JOIN

SELECT c.name, COUNT(o.id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name;

Use COUNT(o.id), not COUNT(*). COUNT(*) counts the row Dana gets from the LEFT JOIN and reports 1; COUNT(o.id) skips NULLs and correctly reports 0.

Other join types

• CROSS JOIN pairs every row with every row. It is useful for generating combinations, and dangerous by accident.
• A self join joins a table to itself under two aliases, for example employees and their managers.
• USING (customer_id) is a shorthand for ON when both columns share a name. PostgreSQL, MySQL and SQLite support it.