LEFT (OUTER) JOIN

A LEFT JOIN (also written LEFT OUTER JOIN — the word OUTER is optional and rarely typed) returns every row from the left table, regardless of whether it has a match in the right table. Where a matching row exists, its columns are filled in normally. Where no match exists, every column that would have come from the right table is filled with NULL instead of the row being dropped.

This makes LEFT JOIN the natural choice whenever the left table represents the complete list you care about, and the right table is optional or supplementary information. The classic example: listing every customer, including ones who have never placed an order.

Worked example

All customers, with their orders if any

SQL

SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id
ORDER BY c.customer_id;

name   | order_id | amount
-------+----------+-------
Alice  | 101      | 250.00
Alice  | 102      | 75.50
Bob    | 103      | 300.00
Carol  | 104      | 120.00
Dave   | NULL     | NULL

Compare this to INNER JOIN, which would have dropped Dave entirely because he has no orders. With LEFT JOIN, Dave still appears — once, since he has zero matching rows — with NULL standing in for order_id and amount. This is exactly what you want when the question is "who are all my customers?" rather than "which customers have ordered something?"

Finding rows that exist only in the left table

LEFT JOIN + WHERE right.id IS NULL

One of the most common and genuinely useful SQL patterns is combining a LEFT JOIN with a WHERE clause that checks for NULL on the right side's key column. Since a NULL there can only happen when there was no match, this pattern isolates exactly the left-table rows that have no corresponding right-table row — for example, every customer who has never placed a single order.

Customers with zero orders

SQL

SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

customer_id | name
------------+------
4           | Dave

This pattern generalizes well beyond customers and orders — "users who never logged in," "products that have never been sold," "employees with no completed training records" are all the same shape: LEFT JOIN from the complete list to the related table, then filter for WHERE related_table.key IS NULL. It is worth memorizing, since it comes up constantly in reporting and data-cleanup queries.

LEFT JOIN keeps every row from the left table, whether or not it has a match on the right.
Unmatched right-side columns come back as NULL rather than dropping the row.
LEFT JOIN + WHERE right_table.key IS NULL finds rows that exist only in the left table — a very common real-world pattern.
Use LEFT JOIN when the left table is the "complete" list and the right table is optional detail.