MySQL Left Join

What Makes LEFT JOIN Different

A LEFT JOIN starts from the table listed first - the 'left' table - and keeps every one of its rows in the result, no matter what. For each row, MySQL looks for a matching row in the second table using the ON condition; if it finds one, the right-hand columns are filled in normally, and if it finds none, those columns come back as NULL rather than the row being dropped. This makes LEFT JOIN the tool of choice whenever the left table's completeness matters more than the match, such as 'every customer, whether or not they've ordered anything yet'.

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

Finding Rows With No Match

Because unmatched rows come back with NULL in every right-table column, you can isolate them with a WHERE ... IS NULL check on a column that could never legitimately be NULL, such as the right table's primary key. This 'anti-join' pattern - LEFT JOIN plus an IS NULL filter - is the standard way to answer 'which rows exist on the left that have nothing on the right'.

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

Counting Related Rows Including Zeros

A plain INNER JOIN with GROUP BY silently drops customers who have no orders, since there's no row to count. Switching to LEFT JOIN keeps them in the result with a count of zero, as long as you use COUNT(o.order_id) - which only counts non-NULL values - rather than COUNT(*), which would count the single NULL-filled placeholder row as one.

SELECT c.customer_id, c.name, COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name;

• table_a LEFT JOIN table_b is equivalent to table_b RIGHT JOIN table_a - the two keywords describe the same shape from opposite directions.
• Every row from the left table appears at least once, and possibly more than once if it matches several right-table rows.
• Placing a right-table filter in the ON clause keeps unmatched left rows in the result; placing the same filter in WHERE can silently remove them.
• LEFT JOIN is the standard pattern for 'all X, with optional Y' reports.