The types
INNER keeps rows matching on both sides. LEFT keeps all left rows, NULLs where the right has no match. RIGHT is the mirror. FULL OUTER keeps everything from both. CROSS produces the Cartesian product.
A self join joins a table to itself — the standard way to walk a hierarchy such as employee → manager.
The fan-out trap
If one order has three line items, joining orders to line_items repeats the order row three times. SUM(orders.total) is now triple-counted.
This is the single most common cause of wrong numbers in analyst SQL. Fix it by aggregating before joining:
SELECT o.order_id, o.total, li.item_count
FROM orders o
LEFT JOIN (
SELECT order_id, COUNT(*) AS item_count
FROM line_items GROUP BY order_id
) li ON li.order_id = o.order_id;Always check your row count before and after a join. If it grew, know why.
WHERE vs ON — the LEFT JOIN killer
-- silently becomes an INNER JOIN
SELECT * FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'shipped';The WHERE runs after the join. Customers with no orders have o.status = NULL, which fails the test, so they are dropped — defeating the entire point of the LEFT JOIN.
Put the condition in ON instead:
LEFT JOIN orders o
ON o.customer_id = c.id AND o.status = 'shipped'This is a classic interview question. The rule: conditions on the outer (preserved) table go in WHERE; conditions on the optional table go in ON.
Set operators
UNION stacks two result sets and removes duplicates; UNION ALL keeps them and is faster — use ALL unless you specifically need dedup.
INTERSECT returns rows in both, EXCEPT (MINUS in Oracle) returns rows in the first but not the second. All require matching column counts and compatible types.
EXCEPT is a quick way to diff two tables during a migration.