Learn how SQL really runs, one idea at a time

Each lesson loads a small dataset and a query, opens the step worth looking at, says what to look for, and gives one change to try.

How a query runs

1. Written order is not execution order
2. An alias exists only after SELECT

Conditions and NULL

1. AND means every condition must be TRUE
2. AND is evaluated before OR
3. IN, LIKE and BETWEEN are ordinary conditions
4. NULL is not FALSE, and it is not TRUE either
5. Testing for NULL needs IS NULL
6. COALESCE gives NULL a default

Computing values

1. CASE picks the first branch that is TRUE
2. Casting and date functions run in SELECT and in WHERE

Ordering and limits

1. ORDER BY comes late, and ties are arbitrary
2. Where NULL sorts is up to you
3. LIMIT without ORDER BY is a coin flip

Joins

1. INNER JOIN keeps only rows that find a partner
2. LEFT JOIN keeps every left row and fills the gaps with NULL
3. A join can multiply rows
4. A WHERE on the right table turns LEFT JOIN into INNER JOIN
5. Rows with no match: the anti-join
6. Joins chain left to right, one step each

Grouping

1. GROUP BY collects rows, then each group becomes one row
2. Grouping by two keys
3. An aggregate without GROUP BY makes one group
4. count(*), count(col) and count(DISTINCT col) count different things
5. WHERE filters rows, HAVING filters groups
6. Finding duplicates with GROUP BY and HAVING
7. DISTINCT is GROUP BY without aggregates

Composing queries

1. A CTE is a named step that runs first
2. A subquery that stands alone runs first
3. EXISTS asks a question per row
4. NOT IN with a NULL in the list keeps nothing
5. UNION stacks two results
6. A recursive CTE runs in rounds

Windows

1. A window function looks at other rows without collapsing them
2. LAG reads the previous row
3. Ranking, and filtering on it with QUALIFY
4. Keeping one row per group with ROW_NUMBER

Debugging

1. Capstone: the revenue is too high