SQL LEFT JOIN — Keep All Rows from the Left Table

What Is a LEFT JOIN?

A LEFT JOIN (also written as LEFT OUTER JOIN) returns all rows from the left table and the matched rows from the right table. When there is no match in the right table, the right-side columns are filled with NULL.

Syntax

SELECT c.id, c.name, o.order_date, o.total
FROM customers AS c
LEFT JOIN orders AS o
  ON c.id = o.customer_id;

Step-by-Step Execution

1. Start with every row in customers.
2. For each customer, look for matching rows in orders where o.customer_id = c.id.
3. If one or more matches exist, output a combined row for each match.
4. If no match exists, output the customer row with NULL in every orders column.

Venn Diagram Interpretation

LEFT JOIN highlights the entire left circle in the Venn diagram. The intersection region contains matched rows (with data from both tables), and the left-only region contains rows that have no counterpart in the right table (right columns are NULL).

NULL Rows Tell a Story

The NULL-filled rows are often the most interesting part of a LEFT JOIN result. They represent records in the left table that have no relationship in the right table. This makes LEFT JOIN the foundation for finding "orphaned" or "missing" data.

Counting and Filtering

To count only customers with orders, add WHERE o.id IS NOT NULL. To find customers without orders, add WHERE o.id IS NULL. This second pattern is called an anti-join and is one of the most important SQL patterns to know.

LEFT JOIN vs INNER JOIN

The key difference: INNER JOIN discards unmatched left rows, while LEFT JOIN preserves them. If every left row has a match, both produce identical results.