What is the difference between INNER JOIN and LEFT JOIN?

The difference is row preservation. An INNER JOIN returns only pairs that satisfy the condition. A LEFT JOIN returns every row from the left input at least once, adding NULLs for right-side columns when no match is found.

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

Row counts and aggregates

• LEFT JOIN can increase row count when the right side has multiple matches
• COUNT(*) counts preserved rows, including those with no match
• COUNT(o.id) ignores NULLs and counts only actual matches
• A filter on the right table in WHERE can turn a LEFT JOIN into an effective INNER JOIN

To filter the right side while preserving unmatched left rows, put the condition in ON rather than WHERE. A WHERE condition such as o.status = 'paid' removes the NULL-extended rows produced for unmatched customers.