SQL Joins, Relationships & Subqueries

LEFT JOIN Silently Becomes INNER JOIN

The most common and hardest-to-detect JOIN bug. Filtering on the right table in WHERE eliminates NULL rows.

-- BAD: WHERE on right table kills the LEFT JOIN
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.status = 'shipped';
-- Customers with NO orders disappear (o.status is NULL for them)