LEFT, RIGHT and FULL joins

An inner join drops rows with no match. An outer join keeps them, filling the missing side with NULL. That one difference answers a whole category of question — "which things have none of that?" — and it comes with two traps that produce wrong answers silently.

`LEFT JOIN`

SELECT count(DISTINCT b.id) AS all_books
FROM books b LEFT JOIN loans l ON l.book_id = b.id;

 all_books
-----------
        40

Forty, where the inner join gave 39. Every book on the left is kept, whether or not it has loans; the book with no loans gets one row with every loans column NULL.

LEFT JOIN is shorthand for LEFT OUTER JOIN. Nobody writes OUTER.

The question it exists for

"Which book has never been borrowed?"

SELECT b.title
FROM books b
LEFT JOIN loans l ON l.book_id = b.id
WHERE l.id IS NULL;

       title
-------------------
 Swami and Friends
(1 row)

This is the anti-join pattern and it is worth memorising: LEFT JOIN, then WHERE <right-hand column> IS NULL.

Read it as: keep every book, attach its loans, then keep only the books where no loan attached.

Test the column you choose carefully — use a column that can never be NULL in a real row, like the right table's primary key. Testing WHERE l.returned_on IS NULL would also match books that are currently borrowed, which is a different question.

Trap 1: counting with `count(*)`

SELECT b.title, count(*) AS with_star, count(l.id) AS with_col
FROM books b LEFT JOIN loans l ON l.book_id = b.id
GROUP BY b.id, b.title
HAVING count(l.id) = 0;

       title       | with_star | with_col
-------------------+-----------+----------
 Swami and Friends |         1 |        0
(1 row)

count(*) says 1. The book has zero loans.

count(*) counts rows, and the phantom row exists — it is the book with NULLs attached. count(l.id) counts non-NULL values, and l.id is NULL on that row, so it counts 0.

With a LEFT JOIN, always count a column from the right-hand table. Never *. This is the single most common off-by-one in reporting, and the number it produces looks perfectly reasonable.

The same applies to sum and avg — they ignore NULL, so they are safe, but COALESCE(sum(l.fine_paise), 0) is what you want if the output feeds arithmetic.

Trap 2: `ON` versus `WHERE`

The one that turns a LEFT JOIN back into an inner join without saying so.

SELECT count(*) FROM books b
LEFT JOIN loans l ON l.book_id = b.id AND l.returned_on IS NULL;

 count
-------
    58

SELECT count(*) FROM books b
LEFT JOIN loans l ON l.book_id = b.id
WHERE l.returned_on IS NULL;

 count
-------
    45

58 against 45, from moving one condition.

In ON, the condition decides which rows match. Books with no matching outstanding loan are still kept, with NULLs — so all 40 books appear, plus extra rows for books with several outstanding loans.
In WHERE, the condition runs after the join and throws away rows. The NULL rows fail l.returned_on IS NULL... except they pass it, because NULL IS NULL is true — which is why this particular pair is confusing. With a condition like WHERE l.returned_on > DATE '2026-01-01', every NULL row would be discarded and the LEFT JOIN would behave exactly like an inner join.

The rule: conditions on the right-hand table go in ON. Conditions on the left-hand table go in WHERE.

If a LEFT JOIN is returning exactly the same rows as an inner join, look for a condition on the right-hand table sitting in the WHERE clause. It is nearly always that.

`RIGHT JOIN` and `FULL JOIN`

SELECT ... FROM a RIGHT JOIN b ON ...     -- keep all of b
SELECT ... FROM a FULL  JOIN b ON ...     -- keep all of both

RIGHT JOIN is just a LEFT JOIN with the tables the other way round, and reading a query where some joins are left and some are right is genuinely hard. Write LEFT JOIN and reorder the tables. You will rarely see RIGHT JOIN in code anybody has to maintain.

FULL JOIN keeps unmatched rows from both sides, filling NULLs in either direction. Its honest use is reconciliation: comparing two lists and finding what is in one and not the other.

SELECT COALESCE(a.id, b.id) AS id,
       CASE WHEN a.id IS NULL THEN 'only in B'
            WHEN b.id IS NULL THEN 'only in A'
            ELSE 'both' END AS status
FROM system_a a FULL JOIN system_b b ON b.id = a.id
WHERE a.id IS NULL OR b.id IS NULL;

That is a genuinely useful thing to know when two systems disagree.

The common report shape

"Everything, with its count, including the zeros":

SELECT b.title, count(l.id) AS times_borrowed
FROM books b
LEFT JOIN loans l ON l.book_id = b.id
GROUP BY b.id, b.title
ORDER BY times_borrowed DESC, b.title;

Every one of the 40 books appears, the never-borrowed one with 0. An inner join would give 39 rows and nobody would notice the missing one.

Whenever a report is "X and how many Y", it wants a LEFT JOIN and count(right.id). That sentence covers a surprising amount of reporting work.

Outer joins and performance

A LEFT JOIN constrains the planner: it cannot drop rows from the left table early, so it has fewer strategies available than for an inner join.

Do not use LEFT JOIN as a default "just in case". If the foreign key is NOT NULL there is always a match, and an inner join is both clearer and faster. Use a LEFT JOIN when you genuinely mean "and possibly nothing".

Check your work

What a LEFT JOIN keeps. Every row from the left, with NULLs where nothing matched.

The anti-join pattern. LEFT JOIN, then WHERE <right key> IS NULL.

Which column to test for IS NULL. One that can never be NULL in a real row — the right table's primary key.

Why count(*) gives 1 for a book with no loans. The phantom row exists; count(*) counts rows.

What to count instead. A column from the right-hand table.

The ON versus WHERE rule. Conditions on the right-hand table go in ON; on the left-hand table, in WHERE.

What it means when a LEFT JOIN returns the same rows as an inner join. A right-hand condition is sitting in the WHERE.

Why not to write RIGHT JOIN. It is a LEFT JOIN with the tables swapped, and mixing directions is hard to read.

What FULL JOIN is genuinely for. Reconciling two lists.

The shape of "X and how many Y". LEFT JOIN plus count(right.id).

When not to use a LEFT JOIN. When the foreign key is NOT NULL and there is always a match.

Practice

Count distinct books with an inner join to loans, then with a LEFT JOIN. Explain the difference of one.
Find the never-borrowed book with the anti-join pattern.
Try the anti-join testing l.returned_on IS NULL instead of l.id IS NULL. Explain why the answer is different and wrong.
Run the count(*) versus count(l.id) comparison and confirm 1 against 0.
Move l.returned_on IS NULL from ON to WHERE and compare the counts — 58 and 45.
Now try a condition that excludes NULL, such as l.borrowed_on > DATE '2026-01-01', in both places. Confirm that in WHERE it turns the LEFT JOIN into an inner join.
Write "every member and how many loans they have", including anyone with none. Insert a member with no loans first.
Write the same for authors and their books.
Rewrite a RIGHT JOIN as a LEFT JOIN by swapping the tables.
Build two small tables with overlapping ids and use a FULL JOIN to list what is only in each.
Take a LEFT JOIN where the foreign key is NOT NULL and change it to an inner join. Confirm the result is identical.
Find any report you have written that counts related rows, and check whether it silently omits the zeros.

Official documentation

PostgreSQL — Joined tables — All the outer join types, with the row-by-row worked example that makes ON versus WHERE clear.
PostgreSQL — The WHERE clause — Where it sits relative to the join, which is the whole trap.
PostgreSQL — Aggregate functions — Why count(col) and count(*) differ on an outer join.

Next: many-to-many, and the join table.

LEFT JOIN