SQL · Beginner · 7 h

Joins, subqueries and set operations

Combine tables with every kind of join, subquery and set operation without losing or duplicating rows, and prove it with row counts.

Why employers care: Wrong joins are the most common source of wrong numbers in dashboards. Interviewers test join behaviour on purpose.

Lessons, in order

SQL INNER JOIN vs LEFT JOIN with real examplesChoose between INNER JOIN and LEFT JOIN by asking which rows must survive, check row counts before and after, find missing matches with an anti-join, and avoid the WHERE clause that silently turns a LEFT JOIN into an INNER one.13 min
SQL FULL OUTER JOIN, CROSS JOIN and SELF JOINReconcile two lists with FULL OUTER JOIN, fill missing months with a CROSS JOIN grid, compare rows of the same table with a SELF JOIN, and join on ranges, all on half a million real retail transactions.15 min
SQL join fan-out: avoid double counting rowsSee why revenue jumps by millions when you join orders to items and payments together, catch duplicate rows from joins with a one-line check, and fix them by aggregating each table to the right grain first.13 min
SQL subqueries: IN, EXISTS and correlated subqueriesUse scalar, derived-table and correlated subqueries, choose EXISTS over a join when you must not duplicate rows, and see on real data why NOT IN returns nothing the moment a NULL appears.12 min
SQL UNION vs UNION ALL, INTERSECT and EXCEPTStack monthly tables with UNION ALL, see the real rows UNION silently deletes, find what is in one period and not the other with INTERSECT and EXCEPT, and reconcile two reports so every missing row is accounted for.13 min