The library / Relational databases

SQL joins: a small reference with predictable results

Use a tiny customer-and-order dataset to see inner joins, left joins, and the filter placement that changes their meaning.

A tiny model

Assume a customers table with Ada (id 1) and Lin (id 2). The orders table has one order, id 100, belonging to Ada.

This dataset makes it easy to check which rows a join preserves.

Inner join: only matches

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

The result contains Ada and order 100. Lin has no matching order and is absent.

Left join: preserve the left side

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

Now Lin appears too, with NULL for order_id.

A left join preserves rows from its left input, but it can still produce multiple rows for one customer when that customer has multiple matching orders.

Filter placement changes the question

Suppose you want every customer, along with any paid orders:

SELECT c.name, o.id AS order_id
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id
 AND o.status = 'paid';

Placing that status test in WHERE instead removes the NULL-extended rows. That may be correct for another question, but it no longer preserves customers with no matching paid order.

Count the right thing

After a left join, COUNT(*) counts the preserved row even when there was no matching order. COUNT(o.id) counts non-NULL order identifiers.

Use the expression that matches your intended meaning.

Before trusting a larger query

Check the expected relationship cardinality. A one-to-many join can multiply rows, and two independent one-to-many joins can multiply them again. Validate a small example before adding an aggregate that hides the duplication.

Keep exploring

Go deeper with the original documentation.

Official documentation
D
DBMinutes Editorial

Practical explanations of database systems, cloud services, and the engineering decisions between them.

AI-assisted content · Our editorial process

Keep the curiosity going.

Back to the library
A little learning goes a long way

Make room for a few good minutes.

Join the list for practical guides, thoughtful comparisons,
and ideas worth bringing to your next project.

Find your next answer

Search concepts, tools, and practical guides.