SQL Quest

Lessons, hints, explained solutions, the in-browser PostgreSQL editor and practice answer checks in your browser are available. Graded assessments, recorded progress from answer checks and new certificates aren't available yet.

Joining Tables

Real questions cross tables: orders need customers, sales need products and categories, shipments need carriers and routes. This track builds a precise mental model of how a join matches rows and how it changes the number of rows, then works through every join type (inner, left, right, full, cross, self), multi-table paths, one-to-many and many-to-many relationships, and the traps that produce confident wrong numbers: missing rows, filters in the wrong clause, and fan-out double counting. You finish able to diagnose a join by counting rows and checking keys, aggregate safely after joining, and match on ranges as well as equal keys.

Start the track

Lessons

  1. Why data lives in several tablesSee why a database splits facts across tables, and write your first join to put them back together.14 min · 4 exercisesFree
  2. INNER JOIN: keep only the matchesUnderstand exactly which rows an inner join keeps, how many rows it returns, and how to write it cleanly on a real schema.16 min · 4 exercises Members
  3. LEFT JOIN: keep every row on the leftKeep every row of the main table, matched or not, and report zeros instead of silently losing rows.16 min · 4 exercises Members
  4. Finding missing records: anti-joinsUse LEFT JOIN … WHERE key IS NULL to find rows that have no partner: customers who never ordered, products never sold, orders never paid.15 min · 4 exercises Members
  5. RIGHT and FULL OUTER JOINRead RIGHT JOIN as a mirrored LEFT JOIN, and use FULL OUTER JOIN to compare two lists and see what is missing on either side.18 min · 4 exercises Members
  6. CROSS JOIN: every combinationPair every row of one table with every row of another to build grids and scaffolds, and recognise the accidental cross join behind exploding row counts.15 min · 4 exercises Members
  7. Self joins: a table joined to itselfJoin a table to a second copy of itself to follow references inside it: employee to manager, customer to referrer, category to parent, and pairs of related rows.18 min · 4 exercises Members
  8. Joining three or more tablesChain joins along the relationships of a schema, keep track of the grain of the result, and avoid the inner join that cancels an earlier left join.20 min · 4 exercises Members
  9. One-to-many relationships and the grain of a joinRecognise one-to-one, one-to-many and many-to-many relationships, and know which values repeat (and must not be summed) when you join a parent to its children.18 min · 4 exercises Members
  10. Many-to-many relationships and bridge tablesJoin through a bridge table to connect two tables that relate many-to-many, count distinct entities on the far side, and pair rows that share a bridge partner.18 min · 4 exercises Members
  11. Row multiplication: the fan-out trapSee how joining two child tables of the same parent multiplies rows, recognise the inflated totals it causes, and fix it by aggregating each child before joining.20 min · 4 exercises Members
  12. Diagnosing joins: counts, keys and totalsProve a join is right before you trust it: count rows before and after, check that the 'one' side's key is unique, count unmatched rows on both sides, and reconcile totals.18 min · 4 exercises Members
  13. Aggregating after joinsCombine joins with GROUP BY safely: group by keys, aggregate where each measure lives, and choose between joining first and pre-aggregating.20 min · 4 exercises Members
  14. ON versus WHERE in outer joinsKnow exactly when a condition belongs in the ON clause and when in WHERE, and why a WHERE filter on the right table silently turns a LEFT JOIN into an inner join.18 min · 4 exercises Members
  15. Non-equi joins: matching on rangesJoin on inequalities and ranges instead of equal keys: price bands, date windows, headcount on a date and overlapping periods.20 min · 4 exercises Members