Start here: one picture carries this whole lesson. Each idea arrives with its technical name first, then where it sits in the picture, so by the end the picture and the SQL mean the same thing to you.

🗂️ The picture for this lesson — a guest list and a pile of RSVP cards

You are planning a wedding, and two things sit on the table. The guest list names everyone you invited, and each guest has a number. The pile of RSVP cards is what came back in the mail, and every card has that guest’s number written on it. The guest list is the leads table. The card pile is the opportunities table. A lead who turned into an opportunity is a guest whose card came back.

Your pipeline lives in several tables. Leads live in one, opportunities in another, deals in a third, and they connect through a shared value: opportunities.lead_id holds the same number as leads.id. That shared value is a key, and a JOIN is the instruction to line two tables up along it. In the picture: the guest number is the key. It is the one thing that tells you which card belongs to which name, and a join is you sitting down and matching each card to its line on the list.

SELECT   l.channel, o.stage
FROM     leads l
JOIN opportunities o ON o.lead_id = l.id

The short letters after each table name are aliases. Once a query touches two tables you need them, because both tables have a column called id and the database will refuse to guess which one you meant. In the picture: the list has guest numbers and each card has its own card number, so the word “number” on its own could mean either pile. l.id is the number on the list. o.lead_id is the guest number written on a card.

This is where numbers start being wrong. The default JOIN, also spelled INNER JOIN, keeps only rows that found a partner on both sides. In the picture: you keep only the guests whose card came back, and everyone who never replied drops off the table. In the Lab’s database, 782 of 1,320 leads never became an opportunity, and an inner join drops all 782. Count the survivors, call it your lead volume, and every conversion rate built on top is inflated. No error, no warning, and a wrong number that looks plausible.

🔧 Tools you will actually hear — Joins in the real world

dbt (data build tool) is how many modern data teams keep joins consistent — you define a model once, and everyone queries that model instead of re-joining by hand. Ask a data team “is there a dbt model for this?” and you will sound like you have been here before.

LEFT JOIN is the fix. It keeps every row from the left-hand table, the one named first, and fills in NULL where the right-hand table had no match. In the picture: you keep the whole guest list and leave the card space blank for anyone who never replied. That blank is NULL, which means “no value here.” A card that reads “zero guests attending” holds a real answer, the number 0. NULL marks the empty space where a card would sit. It fails every comparison you throw at it, including = NULL, so you test for it with IS NULL. Filtering a left join on IS NULL hands you the call list: every guest whose card never came back, the same people an inner join would have hidden.

The trap running the other direction is fan-out: one row becoming several through a join. In the Lab’s database, 69 leads filed two opportunities. Join leads to opportunities and those 69 appear twice, so COUNT(*) reads 607 while the number of people is 538. In the picture: some guests mailed a second card after changing their meal choice. Match cards to names and those guests appear twice, so counting lines overstates who is coming. COUNT(DISTINCT l.id) counts each name once, however many cards they sent. One join can inflate a number and deflate a number at the same time, for two different reasons, which is why the previous unit came first.

Last piece: a CTE. WITH name AS (query) runs a small query first and parks its result under a name, so the rest of the query can use it like a table. In the picture: before you match anything, you go through the card pile and write a clean sheet titled converted, one line per guest who sent at least one card, with the second cards already crossed out. Then you match the guest list against that sheet. The sheet is scratch paper. It exists while this one query runs, and nothing gets saved to the database.

WITH converted AS (
  SELECT DISTINCT lead_id FROM opportunities
)
SELECT COUNT(*) AS guests,
       COUNT(converted.lead_id) AS replied
FROM leads l
LEFT JOIN converted ON converted.lead_id = l.id

Read it top to bottom, the way you would say it: with a sheet called converted, made by listing each person in the card pile once, take the whole guest list, match it against the sheet, and count. The CTE adds no new power. It lets a multi-step query read in the order you thought it through, where the alternative tucks one query inside another and reads inside out. Here it also blocks fan-out, because the sheet already lists each person once and the join has nothing to double. Every query from the next unit onward uses one.

🗂️ The whole picture, one line each

Key — the guest number, on the list and on the card.
Alias — saying which pile’s number you mean.
Inner join — only the guests whose card came back.
Left join — the whole guest list, with blanks where no card came.
NULL — the blank.
Fan-out — a guest who mailed two cards, counted twice.
COUNT(DISTINCT …) — each name once.
CTE — the clean “converted” sheet you write first, then match against.

📜 A bit of history

The reason your rows vanish has a specific origin. Codd introduced NULL in 1979 to represent a value that is missing or unknown, and it has been the most argued-about feature in the language ever since. C. J. Date, one of the field’s most respected theorists, spent decades insisting it should never have existed, because NULL marks the absence of a value, and absences do not behave like values when you compare them. That unresolved forty-year argument is what you are standing in every time an inner join quietly drops a person who had no matching row. The database is working exactly as designed: it is enforcing a philosophical position.