Skip to content
Tutorials Center
My learning

Topics

Lesson 36 of 125 ยท Joins

LEFT JOIN

Learn SQL LEFT JOIN - keep every row of the left table, count zero matches correctly, find missing rows with anti-joins, and see why ON vs WHERE matters.

An INNER JOIN only returns rows that found a partner. That's often what you want, but many questions are about everything, matched or not: all customers with their order counts, including those who never ordered; all courses, even the one without a teacher; every student, with or without enrollments.

LEFT JOIN answers those questions. It keeps every row of the left table, attaches matching rows from the right table where they exist, and fills the right-hand columns with NULL where they don't. In this lesson you'll learn how it works, how to count correctly with it, how to use it to find missing data, and the single most common LEFT JOIN bug: putting a condition in the wrong place.

Keeping every row of the left table

In the school database, the Machine Learning Seminar (CS300) has no teacher yet. An inner join drops it. A left join keeps it:

SQL All courses, with or without a teacher ยท database: school

Press Run (or Ctrl + Enter) to see the result here.

All 10 courses come back. For CS300 the teacher column is NULL, which means "no matching teacher row". The table written before LEFT JOIN is the left table (here courses), and it's the one whose rows are all preserved.

The full keyword is LEFT OUTER JOIN; OUTER is optional and rarely written. A left join is the most common of the three outer joins. The other two, RIGHT and FULL, are covered in RIGHT and FULL OUTER JOIN.

How it works

Extend the nested-loop picture from Why Joins?:

  1. Take a row from the left table.
  2. Find every row of the right table where the ON condition is true, and output one combined row for each.
  3. If there are none, output the left row once anyway, with NULL in every right-hand column.

So a left join returns at least as many rows as the left table has. It can return more, when a left row matches several right rows.

SQL Employees and departments ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The CEO and the contractor have no department_id, so with an inner join they would vanish from any staff list. With a left join they stay, and COALESCE replaces the NULL with a readable label. Remove the WHERE and you'll get all 36 employees, the same count as the table itself.

Counting with LEFT JOIN

"How many orders has each customer placed?" must include customers with zero orders, so it needs a left join. But there's a classic trap in what you count:

SQL COUNT(*) vs COUNT(column) ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first six customers have no orders, yet wrong_count says 1 for each of them. That's because the left join still produces one row for an unmatched customer, the row filled with NULLs, and COUNT(*) counts rows. COUNT(o.id) counts only non-null values of o.id, so the padded row contributes 0.

With a LEFT JOIN, count a column from the right table, ideally its primary key: COUNT(o.id), never COUNT(*). The same applies to SUM: a customer with no orders gets NULL, not 0, so wrap it in COALESCE(SUM(โ€ฆ), 0) when you need a number.

Finding rows with no match (anti-join)

The NULL-padded rows are useful in their own right: they mark exactly the left rows that have no partner. Filter for them with IS NULL on the right table's key and you get the left rows that are missing something:

SQL Customers who never ordered ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Six customers have never placed an order. This pattern is called an anti-join: "rows of A with no matching row in B". It's one of the most useful queries in practice: products never sold, students without enrollments, invoices without payments, orphan rows left after a bad import.

Here it is twice more on the school database, in both directions of the same relationship:

SQL Students and courses without enrollments ยท database: school

Press Run (or Ctrl + Enter) to see the result here.

Three students (Luca, Goran and Wen) have no enrollments, and the only course nobody has taken is CS300, the one without a teacher.

Test a column that can't be NULL in a real match. Use the right table's primary key or the join column itself (o.id, e.student_id). If you tested a nullable column like e.grade IS NULL, you'd also catch real enrollments that simply have no grade yet. NOT EXISTS expresses the same anti-join without this risk, and many people find it clearer.

ON vs WHERE: the classic LEFT JOIN bug

For an inner join, a condition behaves the same in ON or WHERE. For a left join it doesn't, and confusing the two is the most common LEFT JOIN mistake.

Suppose you want every customer with the number of delivered orders they've received. Compare these two queries:

SQL Condition in WHERE vs ON ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first query lists 29 customers; the second lists all 40. Here's why, using the logical order of a query (HAVING has the table):

  • ON decides what counts as a match. In the second query, a customer's non-delivered orders simply aren't matches. A customer with none left still gets one NULL-padded row, so every customer survives, and COUNT(o.id) gives 0.
  • WHERE runs after the join, on its output. In the first query, the padded rows have o.status = NULL, and NULL = 'delivered' is not true. So WHERE deletes exactly the rows the left join worked to keep. The query has quietly become an inner join.

The rule:

  • Conditions on the right table that define which rows match go in ON.
  • Conditions on the left table, which decide which left rows you want at all, go in WHERE.
  • A WHERE condition on a right-table column (other than IS NULL) turns a left join into an inner join. If that's what you want, write JOIN and say so.

Here is the correct report itself, sorted so the zeros are visible:

SQL Every customer with delivered orders ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

c.country = 'Poland' filters the left table, so it belongs in WHERE. o.status = 'delivered' defines a match, so it belongs in ON. All six Polish customers appear, including those with 0 delivered orders.

Chaining LEFT JOINs

When you join a third table after a left join, think about the NULLs you've just created. In this chain, the second join is an inner join:

SQL An inner join after a left join ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first query returns 34 customers, the second 40. For a customer without orders, o.id is NULL, so the inner join to order_items finds no match and removes the row. Once a table is on the optional side of a left join, every later join that depends on it usually needs to be a LEFT JOIN too. Joining Many Tables covers longer chains.

LEFT JOIN in reports

A left join is how reports show zeros instead of hiding them. Here's the number of enrollments per course, including the course that has none:

SQL Enrollments per course, zeros included ยท database: school

Press Run (or Ctrl + Enter) to see the result here.

CS300 appears at the bottom with 0 enrollments and a NULL average, which is honest: there are no grades to average. Use COALESCE if your report needs a placeholder instead.

Old outer-join syntax

Before LEFT JOIN was standardized in SQL-92, some databases had their own notation. You may still find it in old code:

SQL
-- Oracle (legacy): (+) marks the optional side
SELECT c.code, t.name
FROM courses c, teachers t
WHERE t.id (+) = c.teacher_id;

-- SQL Server (legacy, removed in modern versions)
-- WHERE c.teacher_id *= t.id

Oracle still accepts (+) but recommends LEFT JOIN; SQL Server dropped *= long ago. Always write LEFT JOIN โ€ฆ ON in new code.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. The customers table has 40 rows. What is the minimum number of rows customers LEFT JOIN orders ON orders.customer_id = customers.id can return?

    A left join keeps every left row at least once, so you always get at least 40 rows. Customers with several orders add more.
  2. 2. Why does COUNT(*) report 1 for a customer with no orders after a LEFT JOIN?

    Count a right-table column such as COUNT(o.id), which skips the NULL in the padded row and gives 0.
  3. 3. Which query lists customers who have never ordered?

    The left join pads unmatched customers with NULLs, and IS NULL on the right key keeps exactly those. An inner join never produces such rows.
  4. 4. You write customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.status = 'delivered'. What happens to customers without delivered orders?

    WHERE runs after the join. The padded rows have status NULL, which is not equal to 'delivered', so WHERE removes them. Move the condition into ON to keep them.
  5. 5. In a LEFT JOIN b ON โ€ฆ JOIN c ON c.b_id = b.id, what happens to rows of a that had no match in b?

    The inner join needs c.b_id = b.id to be true, which is impossible when b.id is NULL. Use LEFT JOIN for c as well to keep them.

Summary

  • LEFT JOIN (LEFT OUTER JOIN) keeps every row of the left table and fills right-table columns with NULL when there's no match.
  • Count with a right-table column (COUNT(o.id)), not COUNT(*), and use COALESCE for sums that may be NULL.
  • Anti-join: LEFT JOIN โ€ฆ WHERE right.key IS NULL finds rows with no match. Test a key column that can't be NULL in a real match.
  • Right-table conditions that define a match go in ON; left-table filters go in WHERE. A right-table condition in WHERE turns the left join into an inner join.
  • After a left join, later joins on the optional table usually need to be left joins too.
  • Prefer LEFT JOIN โ€ฆ ON over legacy notations like Oracle's (+).

Next: RIGHT and FULL OUTER JOIN, where you'll keep unmatched rows from the right table, or from both.