LEFT JOIN

Explanation

An INNER JOIN throws away rows that don't match. A LEFT JOIN (a.k.a. LEFT OUTER JOIN) keeps every row from the left (first) table, and attaches matching rows from the right table when they exist. When there is no match, the right side's columns come back as NULL.

This is perfect for questions like "show every department and how many employees it has" — even departments with zero employees still appear.

Syntax

SELECT a.col, b.col
FROM table_a AS a
LEFT JOIN table_b AS b
  ON a.key = b.key;

Interactive Example

Show every department with the number of employees in it. Because we start from departments, all five departments appear even if one had none. Then find any department that currently has no employees.

Common Mistakes

• Putting a right-table filter in WHERE. WHERE e.salary > 50000 silently turns your LEFT JOIN back into an INNER JOIN, because NULLs fail the test. Put such filters in the ON clause instead.
• Counting the wrong column. COUNT(*) counts the left row even when the right side is NULL; use COUNT(b.key) to count only matches.
• Confusing left and right. The "left" table is simply the one named first in the FROM clause.

Best Practices

• Start the FROM with the table whose rows you must never lose.
• Use COALESCE on right-side columns so reports read cleanly.
• To detect unmatched rows, test WHERE right_table.key IS NULL.

Practice Question

Using a LEFT JOIN from employees to departments, list every employee's name along with their department name, showing "Unassigned" for anyone whose department is missing (use COALESCE).

Summary

LEFT JOIN preserves all rows of the left table and fills unmatched right-side columns with NULL. Filter on the right table in the ON clause to keep the left rows, or test for IS NULL to find unmatched rows.

FAQ