MySQL LEFT JOIN

A LEFT JOIN returns all rows from the left table, and the matching rows from the right table. When a row in the left table has no match in the right table, the right-side columns are filled with NULL. No row from the left table is ever excluded.

This makes LEFT JOIN ideal for finding records that exist in one table but may or may not have related records in another — for example, all customers including those who have never placed an order.

Syntax

SELECT columns
FROM table_a AS a
LEFT JOIN table_b AS b ON a.id = b.foreign_key;

LEFT JOIN Example

Get all customers, with their order details where available:

SELECT c.name, c.city, o.id AS order_id, o.amount
FROM customers AS c
LEFT JOIN orders AS o ON c.id = o.customer_id;

Sara Begum and Nila Akter are included — even though they have no orders. Their order_id and amount columns show NULL. The orphaned order (customer_id=6) is still excluded because it has no matching customer in the left table.

Finding Rows with No Match

One of the most powerful uses of LEFT JOIN is finding records in the left table that have no corresponding entry in the right table. Filter for NULL on the right-side column after the join:

-- Customers who have never placed an order
SELECT c.name, c.city
FROM customers AS c
LEFT JOIN orders AS o ON c.id = o.customer_id
WHERE o.id IS NULL;

LEFT JOIN with COUNT

Count the number of orders per customer, including customers with zero orders:

SELECT c.name, COUNT(o.id) AS total_orders
FROM customers AS c
LEFT JOIN orders AS o ON c.id = o.customer_id
GROUP BY c.id, c.name
ORDER BY total_orders DESC;

Using COUNT(o.id) instead of COUNT(*) is important here — COUNT(o.id) skips NULL values, correctly returning 0 for customers with no orders, while COUNT(*) would return 1 for those rows.

Key Points to Remember

• LEFT JOIN returns all rows from the left table, plus matched rows from the right.
• Unmatched right-side columns are filled with NULL.
• The left table is the one written before the LEFT JOIN keyword.
• Use WHERE right_table.column IS NULL after a LEFT JOIN to find unmatched records.
• When using COUNT() with a left join, use COUNT(right_table.id) — not COUNT(*) — to get accurate zero counts.