Lesson Description
Keep all rows from the left table.
Lesson 02
Lesson Description
Keep all rows from the left table.
Easy Project
Mini project: Build a people-and-departments report that lists employee names, department names, and validates row counts to prove your join is correct.
JOIN Visual
employees
departments
Match keys first, then verify row counts after the join.
In the previous lesson you joined employees to departments and got 40 rows covering 9 departments. The HR sandbox actually has 10 departments. Accounting has nobody in it, so the inner join had nothing to pair it with and dropped it without a word.
Often that is exactly what you want. But plenty of business questions are about the gaps:
Answering these needs a join that keeps rows even when they have no match. That is LEFT JOIN (also spelled LEFT OUTER JOIN; the word OUTER is optional).
SELECT l.column, r.column
FROM left_table l
LEFT JOIN right_table r
ON r.foreign_key = l.id;
The rule is simple: every row from the left table appears at least once. If a left row has one or more matches on the right, you get one row per match, exactly like an inner join. If it has no match, you still get one row, and every column from the right table is filled with NULL.
"Left" just means the table written before the LEFT JOIN keyword. Choose the table whose rows you must not lose and put it first.
SELECT d.name AS department, e.first_name, e.last_name
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
ORDER BY d.name, e.last_name;
This returns 41 rows: the same 40 employee pairs as before, plus one extra row for Accounting where first_name and last_name are NULL. Swap LEFT JOIN for JOIN and you are back to 40. The difference between the two numbers is precisely the set of unmatched left rows.
Counting is where LEFT JOIN earns its keep. In HR, how many departments does each office location host?
SELECT
l.city,
l.country,
COUNT(d.id) AS departments
FROM locations l
LEFT JOIN departments d ON d.location_id = l.id
GROUP BY l.id, l.city, l.country
ORDER BY departments DESC, l.city;
You get all 10 locations. Seattle hosts 5 departments, five other cities host 1 each, and four locations (Mexico City, Munich, Roma and Utrecht) host none, so they show 0.
The zero comes from COUNT(d.id). Remember from the aggregations course that COUNT(column) skips NULLs. For Munich, the only row in its group has d.id = NULL, so the count is 0. If you had written COUNT(*), it would count that row and report 1 department for Munich, which is wrong. After a LEFT JOIN, always count a column from the right table, ideally its primary key.
To find the unmatched rows themselves, left join and then keep only the rows where the right side came back empty:
SELECT d.first_name, d.last_name, d.specialty
FROM doctors d
LEFT JOIN appointments a ON a.doctor_id = d.id
WHERE a.id IS NULL;
One row comes back: Marcus Wright, an Emergency Physician with no appointments at all. This pattern is called an anti-join, and it is one of the most useful tools in day-to-day analysis.
Test a column that can only be NULL because the match failed. The right table's primary key (a.id) is the safest choice, since primary keys are never NULL in real rows. Testing a nullable column such as a.reason could also catch matched rows that happen to have an empty reason.
You saw NOT EXISTS in the EXISTS lesson. It answers the same question, and PostgreSQL usually plans both forms the same way. Use whichever reads better to you.
Sometimes you only care about certain matches, but you still want every left row. For each doctor, how many scheduled appointments are coming up, and when is the first one?
SELECT
d.first_name,
d.last_name,
COUNT(a.id) AS upcoming,
MIN(a.appointment_date) AS next_visit
FROM doctors d
LEFT JOIN appointments a
ON a.doctor_id = d.id
AND a.status = 'scheduled'
GROUP BY d.id, d.first_name, d.last_name
ORDER BY upcoming, d.last_name;
All 15 doctors come back. Léa Dubois and Marcus Wright show 0 upcoming with a NULL next visit; Sarah Johnson and Daniel Murphy lead with 3 each. The status filter sits in the ON clause, so it decides which appointments count as a match, without removing any doctors. The next section explains why moving it to WHERE would break the query.
Combine the last two ideas to find lapsed customers. Which store customers have placed no order on or after 1 April 2025?
SELECT c.first_name, c.last_name, c.country
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.id
AND o.order_date >= '2025-04-01'
WHERE o.id IS NULL
ORDER BY c.last_name;
Five customers come back. The date condition belongs in ON because it defines what counts as a "recent order". The IS NULL test belongs in WHERE because it filters the final result down to customers who had no such order.
With an inner join, putting a condition in ON or in WHERE gives the same answer. With an outer join, it does not, and this is the number one source of LEFT JOIN bugs.
Logically, PostgreSQL processes the query in this order:
NULLs.A condition in ON shapes the matches. A condition in WHERE runs after the NULL padding, so it can throw away the very rows the LEFT JOIN worked to keep.
Here is the effect on real data from the store sandbox:
| Query | Rows returned |
|---|---|
customers LEFT JOIN orders | 52 (50 orders + 2 customers with none) |
... ON match plus AND o.status = 'cancelled' | 22 (every customer; 3 have a cancelled order) |
... WHERE o.status = 'cancelled' | 3 (only customers with a cancelled order) |
The WHERE version tests NULL = 'cancelled' for every unmatched row. That comparison is unknown, not true, so those rows are discarded and the query behaves like an inner join.
SELECT d.first_name, d.last_name, COUNT(a.id) AS upcoming
FROM doctors d
LEFT JOIN appointments a ON a.doctor_id = d.id
WHERE a.status = 'scheduled'
GROUP BY d.id, d.first_name, d.last_name;
You expect 15 doctors and get 13. Léa Dubois and Marcus Wright vanish, because their padded rows have a.status = NULL. Fix it by moving the filter into ON, as in example 4.
Counting * counts the NULL-padded row, so empty groups report 1 instead of 0. Count the right table's key instead, for example COUNT(d.id) or COUNT(a.id).
FROM appointments a LEFT JOIN doctors d ... keeps every appointment, not every doctor. Since every appointment has a doctor, it behaves like an inner join and Marcus Wright never appears. Ask yourself "whose rows must I keep?" and put that table on the left.
In an anti-join, WHERE a.reason IS NULL is not the same as WHERE a.id IS NULL. The first can match real appointments that have no reason recorded. Test the right table's primary key or the join column itself.
LEFT JOIN and LEFT OUTER JOIN behave the same in PostgreSQL, MySQL, SQL Server and SQLite, including the ON versus WHERE behavior described above. Some older Oracle code uses a (+) marker in the WHERE clause instead of LEFT JOIN; you may see it in legacy systems, but PostgreSQL does not support it.
The exercises below this lesson ask you to find customers who have never ordered, count appointments per doctor while keeping doctors who have none, list every department with its headcount, and total the quantity sold per product including products that never sold. For each one, decide which table must keep all its rows, and check whether you are counting the right column. For the product totals, think about what SUM returns when every value in a group is NULL, and how COALESCE from earlier lessons can turn that into a zero.
LEFT JOIN keeps every row from the left table and fills right-side columns with NULL when there is no match.COUNT(right_table.id), not COUNT(*), so groups without matches report 0.LEFT JOIN ... WHERE right.id IS NULL is the anti-join pattern for finding rows with no match.ON if they define a match; putting them in WHERE silently converts the query into an inner join.LEFT JOIN keeps every row from the left table even when there is no match, filling the right side with NULLs. Use it when the left table defines your list (all departments, all customers) and the right table only adds detail, so nothing silently disappears.
Every customer with their order count
SELECT c.first_name, c.last_name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.first_name, c.last_name
ORDER BY orders DESC;Customers who never ordered still appear, with 0, because COUNT(o.id) skips the NULL from the missing match.
Find rows without a match
SELECT p.name
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
WHERE oi.product_id IS NULL;Products that were never ordered are the rows where the right side came back empty.
WHERE o.id > 100 to the first example drop customers with no orders?For those customers o.id is NULL, and NULL > 100 is unknown, so WHERE removes them. Move the condition into ON to keep them.
Concept check
3 quick questions. One at a time. Instant score at the end.
Return first_name and last_name of customers who have NO orders.
No results yet
Run a query to see results here
Return doctor first_name, last_name, and the number of appointments they have (alias appointments). Include doctors with zero appointments.
No results yet
Run a query to see results here
Return every department name and the number of employees in it (employee_count), including departments with zero employees. Order by name.
No results yet
Run a query to see results here
From the store sandbox, return each product name and total_sold (sum of quantity from order_items). Include products that have never been sold (show 0). Order by total_sold descending.
No results yet
Run a query to see results here