Skip to main content
Joins
2 / 4
0%

LEFT JOIN

0/4 solved4 to unlock next
PrevLocked

Lesson 02

LEFT JOIN

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.

The rows an inner join throws away

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:

  • Which departments have no staff yet?
  • Which doctors have nothing on their calendar?
  • Which customers have not ordered since April?

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).

Syntax

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.

Worked examples

1. Every department, including the empty one

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.

2. Counting matches, including zero

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.

3. The anti-join: finding rows with no match

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.

4. Filtering the right side inside ON

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.

5. Anti-join with a condition

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.

Under the hood: ON versus WHERE

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:

  1. ON decides which right rows match each left row.
  2. Left rows with no match are added back, padded with NULLs.
  3. WHERE then filters the combined rows.

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:

QueryRows returned
customers LEFT JOIN orders52 (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.

Common mistakes

A WHERE filter that silently turns LEFT into INNER

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.

COUNT(*) after a LEFT JOIN

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).

Putting the tables in the wrong order

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.

Testing the wrong column for NULL

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.

Other databases

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.

Practice

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.

Key takeaways

  • LEFT JOIN keeps every row from the left table and fills right-side columns with NULL when there is no match.
  • Use 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.
  • Conditions on the right table belong in ON if they define a match; putting them in WHERE silently converts the query into an inner join.
  • Put the table whose rows you must not lose on the left.

LEFT JOIN: in depth

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.

Worked examples

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.

Common mistakes

  • Filtering the right table in WHERE (WHERE o.order_date >= '2024-01-01') turns the LEFT JOIN back into an INNER JOIN. Put that condition in the ON clause instead.
  • Using COUNT(*) instead of COUNT(right_table.id): unmatched rows count as 1, not 0.
  • Putting the wrong table on the left. The table whose rows you must keep goes first.

Other databases

  • LEFT JOIN and LEFT OUTER JOIN are identical.
  • Oracle's legacy (+) outer-join syntax is still seen in old code; prefer the standard LEFT JOIN.
Check yourself: Why does adding 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

Quick check: LEFT JOIN

3 quick questions. One at a time. Instant score at the end.

Exercise 1

Customers with no orders

Commerce Operations IntelligencemediumPostgreSQL

Return first_name and last_name of customers who have NO orders.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 2

Doctors and their appointment count

Clinical Care PerformancemediumPostgreSQL

Return doctor first_name, last_name, and the number of appointments they have (alias appointments). Include doctors with zero appointments.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 3

All departments with employee count

Enterprise Workforce AnalyticseasyPostgreSQL

Return every department name and the number of employees in it (employee_count), including departments with zero employees. Order by name.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 4

Products with total quantity sold

Commerce Operations IntelligenceeasyPostgreSQL

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.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Joins
2 / 4
0%

LEFT JOIN

0/4 solved4 to unlock next
PrevLocked