LEFT JOIN — Keeping Unmatched Rows

INNER JOIN quietly throws rows away. When a row on the left has no partner on the right, the join simply drops it from the result — correct for some questions, disastrous for others.

Imagine your manager wants a complete staff list with each person's department. An inner join of employees to departments returns fourteen tidy rows and looks perfect, right until somebody notices the fifteenth employee is gone. Omar, the contractor whose department_id is NULL, matched nothing and was deleted from the answer without a single warning.

LEFT JOIN is built for exactly this case. It keeps every row from the left table (the one written after FROM) and fills in the missing right-hand columns with NULL. Reach for it whenever the question demands every row on one side — every employee, every order, every account — whether or not the other side has anything to add.

flowchart LR
  E[employees] --> L[LEFT JOIN]
  D[departments] --> L
  L --> R[Result set]
  style E fill:#0e7490,color:#fff
  style R fill:#0e7490,color:#fff

LEFT JOIN keeps every row from the left table; unmatched right-side cells become NULL.

The mechanics

Syntactically a LEFT JOIN is an INNER JOIN with one extra word. You still name both tables, and you still write an ON condition that says how rows match — the keyword LEFT is the only thing that changes the result.

SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d
  ON e.department_id = d.id;

Read it as: keep every employee, and if a matching department exists, attach its columns. When no department matches, the department value comes back as NULL rather than the whole row vanishing. That single behavioural difference is the entire reason the keyword exists.

Every employee appears; notice the NULL where Omar's department should be.

SELECT e.name, d.name AS department, d.city
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

Finding the rows that have no match

The real power of LEFT JOIN is a trick so common it has a name: the anti-join. You LEFT JOIN, then immediately filter for NULL on a column taken from the right table. Whatever survives that filter is, by definition, the rows on the left that found no partner.

SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE d.id IS NULL;

Why test d.id specifically? Because d.id is the department's primary key — it is NULL only when no department row was attached at all, never when a real department merely has an empty field. Testing a guaranteed-non-null column makes the check unambiguous, which matters once nullable columns enter the picture.

flowchart TD
  A["All employees"] --> J["LEFT JOIN departments"]
  J --> M["Matched rows<br/>d.id is filled"]
  J --> U["Unmatched rows<br/>d.id is NULL"]
  U --> F["WHERE d.id IS NULL<br/>keeps only these"]
  style U fill:#b45309,color:#fff
  style F fill:#0e7490,color:#fff

The anti-join: LEFT JOIN attaches whatever matches, then WHERE d.id IS NULL keeps only the rows where nothing attached.

NULL on the joined side has two causes here — match what you see to the callout below.

SELECT e.name, d.name AS department, d.city
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE d.city IS NULL;

Which table is the "left" one?

The word LEFT refers to position in your query, not to anything about the data. The table you name after FROM is the left table; the one you join to it is the right table. Swap their order and you swap which side is preserved.

This means the same question can be written two ways. "Every department and any employees in it" puts departments on the left; "every employee and any department they belong to" puts employees on the left. Decide which side must be complete, then make sure that table sits immediately after FROM. Getting this backwards is the most common reason a LEFT JOIN quietly returns fewer rows than expected.

You can also chain joins when an answer spans three tables. Each new JOIN follows one more foreign-key pointer, and a LEFT JOIN anywhere in the chain preserves everything to its left.

The trap: a WHERE on the right table undoes the LEFT JOIN

A LEFT JOIN keeps unmatched rows, but only until WHERE runs. WHERE is applied after the join, to every combined row, so a condition that mentions a right-table column quietly throws away the very NULL rows you worked to keep.

-- meant to keep every employee; actually drops anyone not in Oslo
SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE d.city = 'Oslo';

The rule that prevents this: filter the right table inside the ON clause, and filter the left table in WHERE. Move d.city = 'Oslo' into the ON and every employee survives again — those outside Oslo simply get NULL on the department side. If you genuinely want only Oslo employees, an INNER JOIN states that intent more honestly than a LEFT JOIN whose rows are all about to be discarded anyway.

LEFT versus INNER: a decision, not a style

Beginners sometimes treat LEFT as optional decoration and default to INNER JOIN everywhere. That habit hides data. The question to ask before writing any join is simple: should a row with no match disappear?

If the answer is yes — you only want matched pairs, such as employees who actually belong to a department — INNER JOIN is correct and clearer about your intent. If the answer is no — the missing rows matter, like a complete customer list where a customer with no orders must still appear — LEFT JOIN is the only safe choice. Picking deliberately, each time, is what stops quiet data loss from shipping inside a report nobody questions until it is too late.

Exercise

List all employees and their department names. Every employee must appear, even if they have no department. Use a LEFT JOIN.

SELECT e.name, d.name
FROM employees e
-- add your LEFT JOIN here
;

SELECT e.name, d.name FROM employees e LEFT JOIN departments d ON e.department_id = d.id;

Exercise

Find every employee who has no department. Return only their name. Use a LEFT JOIN and filter for NULL.

SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
-- filter here
;

SELECT e.name FROM employees e LEFT JOIN departments d ON e.department_id = d.id WHERE d.id IS NULL;

Exercise

This query is supposed to list every employee with their department, but it uses the wrong join type and silently drops Omar. Change the join so all fifteen employees appear.

SELECT e.name, d.name
FROM employees e
JOIN departments d ON e.department_id = d.id
;

SELECT e.name, d.name FROM employees e LEFT JOIN departments d ON e.department_id = d.id;

Exercise

List each employee's name and their department's city. Keep every employee, and expect NULL to appear for more than one reason: Omar has no department, and the Research department has no city stored. Connect what you see to the callout above.

SELECT e.name, d.city
FROM employees e
-- LEFT JOIN to departments and select the city here
;

SELECT e.name, d.city FROM employees e LEFT JOIN departments d ON e.department_id = d.id;

Exercise

This query is meant to keep all fifteen employees, attaching a department name only when that department is in Oslo (otherwise NULL). The WHERE clause on the right table is deleting every non-Oslo employee instead. Move the Oslo condition out of WHERE so every row survives.

SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE d.city = 'Oslo';

SELECT e.name, d.name FROM employees e LEFT JOIN departments d ON e.department_id = d.id AND d.city = 'Oslo';

Recap

LEFT JOIN keeps every left-table row and fills missing right-side columns with NULL.
INNER JOIN drops unmatched rows; LEFT JOIN keeps them — choose on purpose.
The anti-join pattern filters WHERE right.id IS NULL to surface unmatched rows.
Test a guaranteed non-null right column (a primary key) to detect a missing match cleanly.
A WHERE on the right table quietly reverts a LEFT JOIN to inner — move such filters into ON.

Next you will sort and slice results with LIMIT and OFFSET, paginating through the rows these joins produce.

LEFT JOIN — Keeping Unmatched Rows

The mechanics

Finding the rows that have no match

Which table is the "left" one?

The trap: a WHERE on the right table undoes the LEFT JOIN

LEFT versus INNER: a decision, not a style

Recap

Checkpoint quiz

Go deeper — technical resources