LEFT JOIN — keep all rows from the left table

LEFT JOIN returns every row from the left table. If no matching row exists in the right table, the right-side columns are filled with NULL.

Syntax

SELECT t1.col, t2.col
FROM left_table  t1
LEFT JOIN right_table t2 ON t2.fk = t1.id;

Example: customers and their orders (including those with no orders)

SELECT
  c.first_name,
  c.last_name,
  o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

Customers with no orders appear once with order_id = NULL.

Finding unmatched rows

Filter for NULL on the right side to find rows with no match — a common pattern for finding gaps:

SELECT c.first_name, c.last_name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;   -- customers who never ordered

LEFT JOIN vs INNER JOIN

Aggregating after LEFT JOIN

COUNT(t2.id) returns 0 for unmatched rows (because t2.id is NULL), while COUNT(*) would return 1:

SELECT d.name, COUNT(e.id) AS headcount
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
GROUP BY d.id, d.name;

Tips

• LEFT JOIN is the most commonly used outer join.
• RIGHT JOIN is the mirror image — all right rows kept. Usually rewritten as a LEFT JOIN by swapping table order.