Keeping every row with LEFT JOIN

Keep all rows from one table, find what's missing, and pick the right join for the question.

INNER JOIN drops rows without a match. LEFT JOIN keeps every row from the left table (the one in FROM) and fills the right table’s columns with NULL where nothing matched.

Using the same small tables as lesson 14:

SELECT a.account_id, a.name, o.opportunity_id, o.amount
FROM accounts AS a
LEFT JOIN opportunities AS o
       ON o.account_id = a.account_id;

Cedar stays this time, with NULL deal columns. Deal 104 still drops out, because it only exists in the right table.

Counting with zeros

LEFT JOIN fixes the problem from lesson 12, where groups with no rows disappear:

SELECT a.name,
       COUNT(o.opportunity_id) AS deals
FROM accounts AS a
LEFT JOIN opportunities AS o ON o.account_id = a.account_id
GROUP BY a.name;

Cedar shows 0. Count a column from the right table, not COUNT(*): COUNT(*) counts Cedar’s one NULL-filled row and shows 1.

Finding rows with no match

After a LEFT JOIN, unmatched rows have NULL in every right-hand column. Filter for that to find what’s missing:

-- Accounts with no opportunities at all
SELECT a.account_id, a.name
FROM accounts AS a
LEFT JOIN opportunities AS o ON o.account_id = a.account_id
WHERE o.opportunity_id IS NULL;

Test a right-hand column that can never be NULL in a real match, such as its ID.

The trap: filtering the right table in WHERE

SELECT a.name, o.opportunity_id
FROM accounts AS a
LEFT JOIN opportunities AS o ON o.account_id = a.account_id
WHERE o.stage = 'Negotiation';   -- Cedar disappears again

Cedar’s stage is NULL, and NULL = 'Negotiation' isn’t TRUE, so WHERE throws it away. Your LEFT JOIN has silently become an INNER JOIN. To keep every account, move the condition into ON:

LEFT JOIN opportunities AS o
       ON o.account_id = a.account_id
      AND o.stage = 'Negotiation'

Which join should I use?

When in doubt, start FROM the table whose rows you must not lose, LEFT JOIN the rest, and check the row count.

RIGHT JOIN exists too. It’s a LEFT JOIN with the tables swapped, so most people just swap the tables.

Try it

List every opportunity with its account name and the name of the rep who owns it. Keep deals that don’t have an owner yet. Return the deal name, account name and rep name. Field guide exercise J1 is a longer version of this.

Stretch: which accounts have more than 10 opportunities? That one combines joins, GROUP BY and HAVING: exercise B4.

Write it in the sandbox below, then press Check answer.

Back to the top of the lesson