Joins and reconciliation: SQL patterns and examples
Anti-joins, hierarchies, double-counting and comparing two tables. Joins multiply rows when keys are not unique, which silently inflates totals.
- Customers Without OrdersAn anti-join finds the rows in one table that have no match in another, such as customers who never placed an order.
- Join Fan-Out (Double Counting)Join fan-out happens when joining two one-to-many tables multiplies rows, so totals are counted several times; the fix is to aggregate each side before joining.
- Hierarchy with a Recursive CTEA recursive CTE walks a hierarchy such as an org chart or category tree, returning every level and how deep each row sits.
- Compare Two TablesComparing two tables with a full outer join shows which records are in one source but not the other, and which have different values.