gamesblog

What does this query return?

A tiny table, a short SQL query: say what comes back. COUNT(*) or COUNT(x), NULL in a WHERE, LEFT JOIN counts, and NOT IN with a NULL.

  • Computing
  • Untimed
  • Endless levels

How it plays

Each question shows a table or two of a few rows, with NULLs where they matter, and a query. Say what it returns: a number, or the rows.

Levels go from COUNT(*) against COUNT(x) and WHERE with NULL, to SUM and AVG, GROUP BY, ORDER BY with LIMIT, LEFT JOIN counts, NOT IN with a NULL in the list, and HAVING.

Every answer comes from running the query on a small evaluator of standard SQL as SQLite and PostgreSQL behave, and the query shown is rendered from the very query that runs. The wrong options are the same query run with one misunderstanding — NULL compared like a value, a LEFT JOIN read as inner, LIMIT before ORDER BY.

Keys

Space starts and skips ahead · 1–9 pick · Enter submits · Esc stops. On a phone, tap.

What it trains

This makes predicting what simple SQL queries return — NULLs and joins especially — quicker and surer. It claims nothing wider.

Learn

How to get better at what does this query return?: the method, the techniques, what each new level asks of you, and the traps the wrong answers are made of.

Your approach to each question

  1. Run the query in the order the database does, not the order it is written: FROM and JOIN, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY, then LIMIT.
  2. Find the NULLs in the table before you read the query. Almost every question here turns on what happens to those one or two rows.
  3. Work the rows by hand — there are only a handful. Write down which rows survive the WHERE, then which groups form, then what each aggregate gives.
  4. Before choosing, name the one misunderstanding each wrong option would come from: NULL counted, the NULL group dropped, a LEFT JOIN read as inner. The option that needs none of them is the answer.

Techniques and tricks

  • Three ways to COUNT

    COUNT(*) counts rows, NULLs included. COUNT(x) counts the rows where x is not NULL. COUNT(DISTINCT x) counts the different non-NULL values.

    e.g. x = 3, NULL, 1, 3, NULL, 5: COUNT(*) = 6, COUNT(x) = 4, COUNT(DISTINCT x) = 3

  • NULL makes UNKNOWN, and WHERE drops it

    Any comparison with NULL — =, <>, <, >= — is UNKNOWN, not TRUE or FALSE. WHERE keeps only TRUE rows, so a NULL row never passes, not even a test like x <> 3 that it seems to pass.

    e.g. x = 3, NULL, 1, 3, NULL, 5 WHERE x <> 3: only 1 and 5, so COUNT(*) is 2

  • SUM and AVG skip NULLs

    SUM adds only the values that are there; one NULL does not make it NULL. AVG divides that sum by the count of non-NULL values, not by the number of rows.

    e.g. x = 3, NULL, 1, 3, NULL, 5: SUM = 12, AVG = 12 ÷ 4 = 3.00, not 12 ÷ 6 = 2.00

  • NULLs form one group

    GROUP BY puts every row with a NULL key into a single group of its own; it is not dropped. In that group, COUNT(*) counts its rows and COUNT(key) is 0.

  • LEFT JOIN keeps the empty ones

    A LEFT JOIN keeps every row on the left even when nothing on the right matches; the right-hand columns come back NULL. So an empty dept still appears — with COUNT(*) = 1 for its NULL-filled row, and COUNT(e.id) = 0.

    e.g. Eng has 2 staff, Legal 1, Ops none: COUNT(*) gives Eng 2 · Legal 1 · Ops 1; COUNT(e.id) gives Ops 0

  • One NULL in NOT IN empties it

    x NOT IN (20, NULL) means x <> 20 AND x <> NULL. The second part is UNKNOWN for every row, so no row is TRUE and the count is 0. Without the NULL in the list, it counts the rows whose x is neither 20 nor NULL.

  • HAVING filters groups, after GROUP BY

    WHERE removes rows before grouping; HAVING removes whole groups after. Count the groups first, the NULL group among them, then keep those whose aggregate passes.

    e.g. dept groups 10: 2, 30: 1, NULL: 2 with HAVING COUNT(*) >= 2 → 2 rows

As the levels climb

  • The kinds unlock in this order: COUNT(*) against COUNT(x) and WHERE with NULL; then SUM and AVG and GROUP BY with a NULL; then ORDER BY with LIMIT and LEFT JOIN counts; then NOT IN with a NULL; then HAVING. Four in ten questions come from the newest kind.
  • Every table stays tiny — a few rows — and a little longer as you climb, so you can always work it row by row. The difficulty is in the rules, not the size.
  • Between new kinds, and on every level once all are open, the clock tightens by about 7% a step, down to half. By the end each rule must be instant: see “LEFT JOIN … COUNT(*)” and know the empty group counts 1 before you look at the rows.

Traps to avoid

  • NULL compared like a value: counting a NULL row as passing x <> 3, or NULL = NULL as true. Every comparison with NULL is UNKNOWN; only IS NULL and IS NOT NULL test for it.
  • The NULL group dropped: an option that lists the departments but has no NULL row is the GROUP BY misread. NULL keys group together, and this game lists that group last.
  • A LEFT JOIN read as inner: the empty department vanishes. In a LEFT JOIN it stays, and whether it shows 0 or 1 depends on COUNT(e.id) or COUNT(*).
  • AVG over every row: dividing by the row count, as if NULL were 0, gives a smaller average. Divide by the non-NULL count.
  • LIMIT before ORDER BY, or the sort read the wrong way: LIMIT keeps the first rows after sorting, never the table’s own first rows. Check whether it says DESC — highest first — before you take the top few.

How to practise

  • Use “Kinds of question” to drill the NULL rules alone — “WHERE and NULL” with “NOT IN with a NULL” — until UNKNOWN is a reflex.
  • Switch “Answer by” to “Typing the answer” for the counts, sums and averages; give AVG to 2 decimal places. Result-set questions stay as options.
  • Raise “Options shown” towards 8 so more of the one-slip answers stand side by side and you have to tell them apart, not just spot the odd one out.
  • Read the working on each miss. It names the rule the slip broke, and that rule is all the question was testing.

Levels

It is untimed unless you turn the clock on. There is no last level: at least one dial turns without end. Your level is judged on your last ten answers, every ten: 90% moves you up, below 80% moves you down, which holds most people near four in five right.

All of it is in the settings beside Start — how many answers a judgement looks at and how often it recalibrates, the thresholds, the time, which questions, how many options, and whether you see each verdict at once or review them together at the judgement. You can also jump to any level.

More computing

All games →