SQLSQLiteAdvanced~4h

SQL: Joins & NULL

What comes back when tables meet

Two tables, and everything that goes wrong where they touch. Four modules of saying how many rows a join returns, what a missing value does to a filter, and what the engine just refused, on questions generated fresh that never run out. Then a prompt of your own, two real tables, four questions the gaps can spoil.

Start this project See the modules

Modules: 5
Drills: 9
Build steps: 7
Time: ~4h

Walk out able to

Say how many rows a two-table query returns before running it — and decide, of any predicate over a value that is missing, whether it is true, false, or neither.

What you drill

Pair the rows, or not

INNER, CROSS and self joins, and the count each returns.

Keep the side with no match

LEFT, RIGHT, FULL — and the WHERE that quietly undoes them.

A third truth value

Why NULL = NULL is not true, and what a WHERE drops.

Group it, then count it

GROUP BY, HAVING, and the denominator under AVG.

The pitch

What you practise, and what you leave with

Two related tables in your own database — twelve members, twenty repairs, real gaps in four columns — and four questions, one query each.

You will practise

Rows an INNER, LEFT, RIGHT or FULL join returns
ON against WHERE, and the anti-join
NULL = NULL, IS NULL, and the NOT IN trap
GROUP BY, HAVING, COUNT(*) against COUNT(col)

Afterwards you can

Say how many rows a join returns before running it
Keep the unmatched rows through a filter
Decide a predicate over a missing value
Choose the count that answers your question

Modules

5 modules, 25 items

Lessons explain one idea. Drills repeat it until it sticks. Build steps make something that exists afterwards.

1
Pair the rows
~42 min
Say how many rows two tables produce when they meet, product and all.
- LessonA join is a product with a filter12 min
- DrillCount the pairs a join makes12 min
- LessonOne table, written twice7 min
- DrillCount pairs without counting twice11 min
2
Keep the side with no match
~38 min
Pick the join that keeps the rows you care about, and keep them through a filter.
- LessonThe rows with no partner8 min
- DrillPick the join that keeps them10 min
- LessonON decides pairs, WHERE deletes9 min
- DrillMove the filter and watch them go11 min
3
The third truth value
~40 min
Decide what a predicate over a missing value is worth, and which rows survive it.
- LessonWhy NULL = NULL is not true10 min
- LessonThe tests that can be true9 min
- DrillDecide what the engine cannot11 min
- DrillCount the rows a gap hides10 min
4
Group it, and count what is there
~50 min
Say how many rows a GROUP BY returns, and pick the count that answers the question.
- LessonOne row per group8 min
- LessonWHERE filters rows, HAVING groups6 min
- DrillCount the groups, not the rows9 min
- LessonThree counts, and a denominator6 min
- DrillCount what is actually there10 min
- DrillWrite the clause you meant11 min
5
Two tables with gaps in them
~58 min
Put two real tables in front of you and answer four questions the gaps can spoil.
- Build stepGet a SQL prompt4 min
- Build stepLoad the two tables7 min
- Build stepFind the members who never came9 min
- Build stepCount per member, keep everyone11 min
- Build stepTotal by town, keep the big ones10 min
- Build stepAverage only what was recorded9 min
- Build stepAsk it something of your own8 min