Module
Module 4 of 6Lesson 2 of 2~14 min

Join traps, and how to detect them

A badly written join runs without errors and returns a number that looks plausible but is wrong. The lesson walks you through the most costly mistakes in product analyses and a simple way to catch them before you share a result. Enough to stand behind your revenue or usage numbers in front of the data team.

Take this lesson: your first course is freeSee the full syllabus

Lesson objective

By the end of this lesson, you will be able to recognise and fix the two most costly join errors (row fan-out that inflates sums, and a filter placed in WHERE that cancels a LEFT JOIN), by reasoning about the grain of each table and checking the row count.

Topics covered

  • join errors
  • table grain
  • LEFT JOIN
  • SQL duplicates
  • result check

Where it fits

Combine tables

How do I combine several tables without losing rows or inflating my numbers?

Lessons in this module

  1. Join tables with INNER JOIN and LEFT JOIN
  2. Join traps, and how to detect them (this lesson)

What you will learn in the course

This lesson is part of the course Model your product’s data and query it with SQL

  • Read a data model (tables, primary and foreign keys, cardinalities, entity-relationship diagram) and connect it to the product's screens and rules.
  • Model the data of a feature (entities, attributes, relationships, keys, cardinalities) and get the model validated by the engineering team.
  • Compare a relational database and a document (NoSQL) database for a product need and justify the choice based on expected queries, consistency and change.
  • Write queries that filter, sort and aggregate (SELECT, WHERE, ORDER BY, GROUP BY, HAVING) while handling NULL values and dates correctly.
  • Combine several tables with INNER and LEFT joins, and check that the result neither loses nor duplicates any row.
  • Turn a product question into a verifiable query, using CTEs and simple window functions (ROW_NUMBER, LAG, SUM OVER).
  • Query data read-only while protecting personal data, and have an AI draft SQL while checking every query before using its result.

Related courses