Keeping unmatched rows

What does LEFT JOIN keep that JOIN drops?

An inner join returns only matched pairs. Ask it for members and their loans and the members who never borrowed vanish, because no loan row pairs with them. For a question like "how many loans has each member made?", that is wrong: Noor and Ola have made zero, and zero is an answer.

LEFT JOIN keeps every row of the table on its left, the one in FROM, whatever happens on the right. Members with loans get one row per loan, as before. Members without any get exactly one row, with the loan columns filled with NULL.

Run the query below and compare it with the same query using plain JOIN. The inner version gives eight rows, one per loan. The left version gives ten: the same eight, plus Noor and Ola with NULLs. Then open the Rows view and look at the JOIN step, which marks the rows that were kept without a match.

SELECT m.name, l.id AS loan_id, l.loaned_on
FROM members m
LEFT JOIN loans l ON l.member_id = m.id
ORDER BY m.id;

Where do the NULLs come from?

When a left row has no partner, the database still has to fill every column of the right table in the output. It fills them with NULL, meaning "no value here". The NULLs are made up by the join; nothing in the loans table is NULL for Noor, because she has no rows there at all.

That creates one ambiguity to keep in mind. loans.returned_on is also NULL for books that are still out. In a left join result, a NULL returned_on can mean "this loan is still open" or "there is no loan". To tell them apart, look at a column that can never be NULL in a real loan, such as the primary key l.id. If l.id is NULL, the row is padding.

SELECT m.name, l.id AS loan_id, l.returned_on
FROM members m
LEFT JOIN loans l ON l.member_id = m.id
WHERE m.id IN (1, 5);

How do you find rows that have no match?

Left join, then keep only the padded rows: WHERE l.id IS NULL. Every member who borrowed something produced rows with a real l.id, so they are filtered out. What remains are the members with no loans at all.

This pattern answers a whole family of questions. Books never borrowed: left join books to loans and keep l.id IS NULL. Customers who never ordered, species nobody has seen, parts never fitted: same shape every time.

Test the right table's primary key, or any column declared NOT NULL. Testing a column that can be NULL in real data, like returned_on, mixes the padded rows with genuine open loans and gives the wrong list.

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

How do you count when some rows have no match?

COUNT(*) counts rows. A member with no loans still has one row after a left join, the padded one, so COUNT(*) reports 1. That is the bug in the demo starter once you switch it to LEFT JOIN.

COUNT(column) counts only the non-NULL values in that column. COUNT(l.id) is 0 for the padded row and counts each real loan once. Use it whenever you count matches after a left join. SUM behaves similarly: summing a column that is NULL for every row in a group gives NULL rather than 0. Wrap it in COALESCE(SUM(...), 0) if you need a number.

Group by the left table's key, m.id, so that two members who happen to share a name are still counted separately.

SELECT m.name, COUNT(*) AS rows_seen, COUNT(l.id) AS loans
FROM members m
LEFT JOIN loans l ON l.member_id = m.id
GROUP BY m.id;

Why does a WHERE condition undo a LEFT JOIN?

This trap catches almost everyone. Suppose you want every member with the number of books they have out right now. You left join, then add WHERE l.returned_on IS NULL. Kit and Mia vanish. Both have borrowed, both returned everything, so all their rows have a date in returned_on and the WHERE throws every one of them away. The left join kept them; the filter removed them a step later.

The fix is to move the condition into ON: LEFT JOIN loans l ON l.member_id = m.id AND l.returned_on IS NULL. In ON, the condition decides which loans attach to each member. A member whose loans all fail it is still kept, with padding, because that is what a left join promises.

A rule of thumb: a condition on the right table that should shape which matches count goes in ON. A condition that should remove rows from the final result goes in WHERE. A condition on the left table, such as members who joined this year, belongs in WHERE. Follow both versions in the Rows view and watch which step drops Kit.

SELECT m.name, COUNT(l.id) AS out_now
FROM members m
LEFT JOIN loans l ON l.member_id = m.id
  AND l.returned_on IS NULL
GROUP BY m.id;

Does the order of the tables matter?

For LEFT JOIN, yes. The table in FROM is the one that keeps all its rows. FROM members LEFT JOIN loans keeps every member. FROM loans LEFT JOIN members keeps every loan, and since every loan has a member, that is no different from an inner join here.

Before writing the query, say the question out loud and find the word "every": every member, every book, every customer. That table goes in FROM, and everything else is left joined to it.

What about RIGHT JOIN and FULL JOIN?

RIGHT JOIN is the mirror image: it keeps every row from the table after the JOIN keyword. FROM loans l RIGHT JOIN members m gives the same rows as FROM members m LEFT JOIN loans l. Most people stick to LEFT JOIN and put the "every" table first, which keeps queries easier to read.

FULL JOIN (or FULL OUTER JOIN) keeps unmatched rows from both sides, padding whichever side is missing. It is useful for comparing two lists, such as last year's members against this year's.

SQLite added both in version 3.39, in 2022, so older copies of SQLite reject them. PostgreSQL and SQL Server have always had both. MySQL has RIGHT JOIN but no FULL JOIN.

SELECT m.name, l.id AS loan_id
FROM loans l
RIGHT JOIN members m ON m.id = l.member_id
ORDER BY m.id;

How can you check a left join result?

Left joins hide their mistakes well, so check them on purpose.

• Count the left table's rows first. A per-member result should have exactly as many rows as members, here six.
• Find a row you know has no matches, such as Noor, and make sure it shows 0 or NULL rather than 1 or nothing.
• Look for WHERE conditions on right-table columns. Unless the condition is IS NULL on a key, it probably belongs in ON.
• Use the Rows view to watch the JOIN step keep unmatched rows and the WHERE step decide whether they survive.

Field notes

• LEFT JOIN keeps every row of the table in FROM, matched or not.
• Unmatched rows get NULL in every column from the right-hand table.
• WHERE right_table.id IS NULL finds rows that have no match at all.
• Count with COUNT(l.id), since COUNT(*) gives 1 for an unmatched row.

Easy to get wrong

COUNT(*) counts the padded row, so members with no loans show 1.

In WHERE, the date test drops every member without a March loan, which turns the left join back into an inner join.

returned_on is also NULL for open loans. Test the key to find books never loaned.

The table in FROM keeps all its rows. To list every member, members must come first.

Questions people ask

Is LEFT OUTER JOIN different from LEFT JOIN?

No. OUTER is optional and changes nothing.

Can I chain several left joins?

Yes. Each one keeps everything built so far. Be careful mixing them with inner joins later in the chain, which can drop the padded rows again.

How do I show 0 instead of NULL for a missing total?

Use COALESCE(SUM(l.fine_pence), 0). COALESCE returns the first value that is not NULL.

Why not always use LEFT JOIN to be safe?

It changes the meaning. When you only want matched pairs, an inner join says so clearly, and a left join adds NULL rows you then have to filter out.