Blog

SQL JOINs 2: Self Join, Non-Equi Join, Semi/Anti Join and CROSS APPLY vs LATERAL

Joining an order's items and payments together multiplies the totals (fan-out); the fix is to group first and join afterwards. We show CROSS JOIN report skeletons, a self join on the category tree, non-equi range joins, semi joins with EXISTS, anti joins with NOT EXISTS (same plan as LEFT JOIN … IS NULL), and every customer's last three orders with T-SQL CROSS/OUTER APPLY versus standard LATERAL in PostgreSQL and MySQL, with real output.