LEFT JOIN: keep every row from one side
A LEFT JOIN keeps every row from the left (first) table, joins matching rows from the right table, and fills the right-hand columns with NULL where there is no match.
Why it matters
"Show every department with its headcount" must include HR with 0. "Every customer and what they spent" must include Omar, who spent nothing. An INNER JOIN silently drops them. LEFT JOIN keeps them.
The idea
In departments d LEFT JOIN employees e, departments is the left table and every department is kept. Where employees match, you get one row per match, as with INNER JOIN. Where none match — HR — you still get one row, with NULL in every employees column.
Count with COUNT(e.emp_id), not COUNT(*), so HR's NULL row counts as 0.
RIGHT JOIN is the mirror image (keep every row of the right table); people almost always swap the tables and write a LEFT JOIN instead.
A class register. Every pupil's name is on it (the left table). Next to each name you write the homework they handed in (the right table). A pupil who handed nothing in stays on the register with a blank next to their name.
A blank on a register is just empty. In SQL the blank is NULL, and NULL behaves strangely: COUNT(*) still counts that row as 1, and a WHERE on a right-hand column can throw the row away again.
Syntax
SELECT a.column, b.column
FROM table_a AS a
LEFT JOIN table_b AS b ON b.key = a.key;How it works
- Start with every row of the left table, departments: Engineering, Sales, Marketing, HR.
- For each, find matching employees. Engineering has 4, so it appears 4 times.
- HR has no matching employees, so it appears once, with NULL for the employee columns.
COUNT(e.emp_id)counts only non-NULL ids: 4, 2, 1 and 0 for HR.COUNT(*)would wrongly give HR 1, because there is one (NULL-filled) row.
Key points
- LEFT JOIN keeps every row of the left table; unmatched rows get NULLs on the right.
- Put the table you want to keep in full first ("every customer" → customers first).
- Count the right side with
COUNT(right.column), notCOUNT(*), to get 0 for no matches. - Wrap sums of the right side in
COALESCE(…, 0). - RIGHT JOIN is LEFT JOIN with the tables swapped; prefer LEFT JOIN for readability.
Common mistakes
- Using
COUNT(*)after a LEFT JOIN and getting 1 instead of 0 for rows with no match. - Adding
WHERE right_table.column = …, which throws the NULL rows away and turns the LEFT JOIN back into an INNER JOIN (see Join traps). - Putting the tables the wrong way round, so the side you wanted to keep is on the right.
Not true: LEFT JOIN … WHERE right.col = x still keeps the left rows that had no match.
In fact: Those rows have NULL on the right, so the WHERE condition is not true for them and they are dropped. Put the condition in ON to keep them.
In the interview
A LEFT JOIN returns every row from the left table and the matching rows from the right; where there is no match, the right-hand columns are NULL. I use it whenever the question says "all" or "every" — every customer, including those with no orders. After a LEFT JOIN I count a right-hand column rather than COUNT star, so unmatched rows give zero, and I COALESCE sums to zero.
Part of Unit 3: Joins: combining tables in the free SQL course.