Lesson 7: LEFT JOIN, RIGHT JOIN & FULL OUTER JOIN

LEFT JOIN keeps all rows from the left table even without a match on the right — essential for 'show everything, including gaps'.

Real-time example

List every customer and their total orders, including customers who've never placed one (0, not missing).

SELECT c.name, COALESCE(SUM(o.amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

What's happening

A customer with no orders still appears because LEFT JOIN keeps every customers row; the unmatched orders columns come back as NULL, which COALESCE turns into 0.

Use LEFT JOIN whenever the business question is 'including the ones with nothing', not just 'the ones with matches'.