LEFT JOIN

What is a LEFT JOIN?

A LEFT JOIN combines rows from two tables based on a matching column. It returns every row from the left table, and the matching rows from the right table. When no match exists in the right table, the columns from the right table are filled with NULL.

SELECT customers.first_name, customers.last_name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

Every customer appears in the results — even those who have never placed an order. Those customers will show NULL for order_id.

LEFT JOIN vs INNER JOIN

The key difference is how unmatched rows are handled:

INNER JOIN — only returns rows where a match exists in both tables. Customers with no orders would not appear at all.

LEFT JOIN — returns all rows from the left table regardless of whether a match exists. Customers with no orders appear with NULL values for the order columns.

-- INNER JOIN: only customers who have placed at least one order
SELECT customers.first_name, orders.order_id
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;

-- LEFT JOIN: all customers, NULL for those with no orders
SELECT customers.first_name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

Finding rows with no match

One of the most useful patterns with LEFT JOIN is finding rows in the left table that have no corresponding row in the right table. You do this by filtering for NULL in a right-table column after the join.

-- Find all customers who have never placed an order
SELECT customers.first_name, customers.last_name
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
WHERE orders.order_id IS NULL;

Because unmatched rows produce NULL in the right table columns, filtering for IS NULL isolates exactly those unmatched rows.

LEFT JOIN with GROUP BY

You can combine LEFT JOIN with GROUP BY to aggregate across the joined tables. A common use case is counting how many related records each row in the left table has — including a count of zero for rows with no matches.

-- Count orders per customer, including customers with zero orders
SELECT customers.first_name, customers.last_name,
       COUNT(orders.order_id) AS order_count
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY customers.customer_id, customers.first_name, customers.last_name;

Notice COUNT(orders.order_id) rather than COUNT(*) — this counts only non-NULL values, so customers with no orders correctly get a count of 0.

Common mistakes

Filtering on a right-table column in WHERE instead of ON — writing WHERE orders.status = 'shipped' after a LEFT JOIN turns it into an effective INNER JOIN, because rows with NULL for orders.status won't satisfy the condition. If you need to filter on the right table while keeping unmatched left rows, put the condition in the ON clause instead.

Using COUNT(*) instead of COUNT(right_column) — COUNT(*) counts every row including those with all-NULL right-table columns, so unmatched left rows would be counted as 1 instead of 0. Use COUNT(right_table.column) to get accurate zero counts.

Confusing which table is left and which is right — the table after FROM is always the left table. Swap the table order if you need to preserve rows from the other table.