Blog
Mert Özen's blog posts on software development, backend, frontend, and DevOps.
SQL JOINs 1: INNER JOIN, LEFT/RIGHT/FULL OUTER JOIN and the ON vs WHERE Difference
Joins match rows; they are not Venn diagrams: INNER JOIN keeps matched pairs, and LEFT/RIGHT/FULL OUTER JOIN also keep the preserved side's unmatched rows with NULLs. Filtering the right table of a LEFT JOIN in WHERE silently turns it into an INNER JOIN, which we see as Inner Join in the plan. We look at books without reviews, customers without orders, stock reconciliation with FULL OUTER JOIN and how one-to-many joins multiply rows, with real output.
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.