Every explanation of SQL joins seems to reach for the same overlapping-circles diagram, and many people still come away unsure which join to use. It helps to drop the diagram and ask one plain question: what happens to a row that has no match in the other table?

That single question is the whole difference between INNER JOIN and LEFT JOIN. Everything else about them is the same.

A tiny example

Take two small tables. customers holds three people, and orders holds the purchases they made:

customers                orders
id | name                id | customer_id | amount
---+-----                ---+-------------+-------
 1 | Ada                 10 |           1 |     50
 2 | Ben                 11 |           1 |     30
 3 | Cy                  12 |           2 |     20
                         13 |          99 |     70

Ada has two orders, Ben has one, and Cy has none. Order 13 points at customer 99, who doesn’t exist. Two rows here have no match: Cy on one side, order 13 on the other. The joins differ in what they do with them.

INNER JOIN keeps only the matches

SELECT customers.name, orders.amount
FROM customers
INNER JOIN orders ON orders.customer_id = customers.id;
name | amount
-----+-------
Ada  |     50
Ada  |     30
Ben  |     20

Only rows that found a partner appear. Cy is gone because Cy has no orders, and order 13 is gone because it has no customer. INNER JOIN drops anything without a match, on both sides. Writing JOIN on its own means the same thing.

LEFT JOIN keeps everything from the left table

SELECT customers.name, orders.amount
FROM customers
LEFT JOIN orders ON orders.customer_id = customers.id;
name | amount
-----+-------
Ada  |     50
Ada  |     30
Ben  |     20
Cy   |   NULL

Now Cy is back, with NULL where the order columns would be. The “left” table is the one written before the JOIN keyword, here customers. A LEFT JOIN keeps every row from that table, whether or not it found a match, and fills in NULL for the right table’s columns when it didn’t. Order 13 is still absent, because it lives in the right table and has no customer.

So the choice comes down to this: if a row without a match should still show up in your results, use LEFT JOIN. If it should disappear, use INNER JOIN.

A useful trick: finding what’s missing

Because LEFT JOIN fills in NULL for non-matches, you can use it to find rows that have no match at all. Filter for the NULL:

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

That’s “customers who have never ordered.” It’s one of the most useful things a LEFT JOIN does, and the same pattern answers any “which X have no Y” question. Note that it uses IS NULL, not = NULL. If that distinction is new to you, Why ‘WHERE x = NULL’ Never Works in SQL explains it.

How to practice choosing

Before you write a join, say the result out loud: “I want every customer, and their orders if they have any” (that’s LEFT), or “I want only customers who have orders” (that’s INNER). The wording tells you which to use. Then run it on a small table where you know the answer, like the one above, and check that the row you expected to survive did.

If you ever get stuck, count rows. Run SELECT COUNT(*) on the left table alone, then on the join. With an INNER JOIN, the join can come back with fewer rows than the left table, because unmatched rows disappeared. With a LEFT JOIN, every left row is still there, so you won’t see fewer than the left table has.

Two mistakes worth knowing about ahead of time

Filtering the right table in WHERE and accidentally undoing your LEFT JOIN. This catches almost everyone once. Suppose you want all customers, plus only their orders over 25:

SELECT customers.name, orders.amount
FROM customers
LEFT JOIN orders ON orders.customer_id = customers.id
WHERE orders.amount > 25;
name | amount
-----+-------
Ada  |     50
Ada  |     30

Ben and Cy vanished. For them, orders.amount is NULL after the join (Ben’s only order is 20), and NULL > 25 is never true, so WHERE throws those rows out. You’ve turned your LEFT JOIN into an INNER JOIN without noticing. To keep every customer, put the condition in the ON clause, so it limits which orders match instead of which rows survive:

SELECT customers.name, orders.amount
FROM customers
LEFT JOIN orders
  ON orders.customer_id = customers.id AND orders.amount > 25;
name | amount
-----+-------
Ada  |     50
Ada  |     30
Ben  |   NULL
Cy   |   NULL

Counting with COUNT(*) after a LEFT JOIN. COUNT(*) counts rows, and Cy’s row exists even though there’s no order, so Cy shows as 1. Count a column from the right table instead, such as COUNT(orders.id), which skips NULL and correctly gives Cy 0. And if a join ever returns more rows than you expected, the cause is a different one, covered in Why Your JOIN Doubled Your Totals.

A habit that makes joins easy

Treat LEFT JOIN as your starting point and INNER JOIN as a deliberate choice. Ask yourself first which table is the one you really care about, the one whose rows must not vanish, and put it on the left. Then decide whether unmatched rows belong in the answer. Doing it in that order turns a choice that feels like guesswork into two short questions, and it makes the “where did my rows go?” surprise much rarer.