INNER JOIN and LEFT JOIN
A join combines rows based on a relationship. INNER JOIN returns matching pairs only. LEFT JOIN preserves every row from the left input and fills right-side columns with NULL when no match exists. Use table aliases and qualify columns to avoid ambiguity.
A common mistake is placing a filter on the right-side table in WHERE after a LEFT JOIN. That can remove NULL-extended rows and make the result behave like an inner join. If the condition determines which right-side rows may match while preserving all left rows, it may belong in the ON clause instead.
Join customers and orders
SELECT c.id, c.name, o.id AS order_id, o.total
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id
ORDER BY c.id, o.id;💡 This returns all customers, including those with no orders. For customers without orders, the order columns are NULL.
Key Takeaways
- INNER JOIN keeps matches; LEFT JOIN preserves all left-side rows.
- Use aliases and qualify shared column names.
- WHERE filters can remove unmatched rows after a LEFT JOIN.
Knowledge Check
Which join preserves all rows from the left table even when there is no match?