SQL problem

COUNT(*) vs COUNT(column) after a LEFT JOIN

Your LEFT JOIN worked. It kept the unmatched row alive by padding every column from the other table with NULL, and that padded row is still a row. COUNT(*) counts rows without looking inside them, so it reports 1 where you expect 0.

The symptom

Both counts are doing what they are defined to do, and they answer different questions. One counts the rows the join produced for Cy. The other counts the rows that carry a real submission. Cy has one of the first and zero of the second.

The two tables

Ada submitted twice and both were marked. Bo submitted twice and one of them has no score yet: a real row with a missing value. Cy has no row in submissions at all. Those two kinds of absence look identical in a finished report and they have different causes.

The join output, before any grouping

SELECT s.name, sub.submission_id, sub.score
FROM students s
LEFT JOIN submissions sub ON sub.student_id = s.student_id
ORDER BY s.name, sub.submission_id;

Which left row matched which right rows

• Ada matched submissions 5001 and 5002. Two pairs, two output rows.
• Bo matched submissions 5003 and 5004. Two pairs, two output rows, and the NULL in the second one came out of the stored data.
• Cy matched nothing. A LEFT JOIN emits the left row anyway and invents NULLs for every right-side column you asked for. The NULLs in that fifth row exist only in this result; no blank row sits in submissions.

Five rows. Every count on this page is computed over these five, so any surprising number traces back to one of them.

The three counts, side by side

SELECT s.name,
       COUNT(*)                  AS count_star,
       COUNT(sub.submission_id)  AS count_submission_id,
       COUNT(sub.score)          AS count_score
FROM students s
LEFT JOIN submissions sub ON sub.student_id = s.student_id
GROUP BY s.name
ORDER BY s.name;

COUNT(*) counts the rows in each group. Cy's group holds the single padded row, so the answer is 1. COUNT(sub.submission_id) counts the rows where that expression is not NULL. Cy's one row has NULL there, so the answer is 0, which is the number you were reporting.

Then look at Bo. COUNT(sub.score) says 1 and Bo submitted twice. That NULL is a real submission whose score has not been entered. The first mistake announces itself the moment a student who submitted nothing shows a 1. This one is quieter, because it is wrong only for the rows where the attribute happens to be missing.

The fix: count the column that proves the event

Say the sentence out loud before you pick the argument. "How many submissions does this student have" is proved by a submission's identity, so count sub.submission_id, which is never NULL when a real submission matched. "How many scored submissions" is a different sentence and COUNT(sub.score) is the right answer to it. COUNT(*) answers "how many rows did this join produce for the group", which is a useful question while you debug a fan-out.

The same three counts over the whole join, with no GROUP BY:

SELECT COUNT(*)                        AS count_star,
       COUNT(sub.submission_id)        AS count_submission_id,
       COUNT(sub.score)                AS count_score,
       COUNT(DISTINCT s.student_id)    AS students_seen
FROM students s
LEFT JOIN submissions sub ON sub.student_id = s.student_id;

Five joined rows, four submissions, three of them scored, three students.

Keeping the zeros, and the filter that deletes them

To list the students who submitted nothing, filter on the count of the key:

SELECT s.name, COUNT(sub.submission_id) AS submissions
FROM students s
LEFT JOIN submissions sub ON sub.student_id = s.student_id
GROUP BY s.name
HAVING COUNT(sub.submission_id) = 0
ORDER BY s.name;

That returns one row, Cy with 0. HAVING COUNT(*) = 0 in its place returns nothing, because every group has at least one row by the time HAVING runs.

There is a second way to lose Cy. Add WHERE sub.score >= 80 to that query and the result is Ada 1 and Bo 1: Cy is gone, because WHERE runs after the NULL padding and a NULL score fails a >= 80 test. Move the same condition into the ON clause, as AND sub.score >= 80, and the result is Ada 1, Bo 1 and Cy 0. In ON it decides which rows match. In WHERE it decides which output rows survive.

Check yourself

Take the three-count query above and change LEFT JOIN to a plain JOIN. Before running it: what happens to Cy, and what do Ada and Bo report for COUNT(*) and COUNT(sub.submission_id)?

Dialect note

COUNT(*) counting rows and COUNT(expression) skipping NULLs is standard SQL, and the ON versus WHERE behaviour follows from clause order. Every statement and every result table on this page was executed before publishing: on DuckDB 1.5.3, the engine family the game uses to grade queries in your browser, and again on SQLite 3.53, which returned identical rows. Postgres and MySQL 8 return the same numbers for all of it.

One portability habit is worth copying from these queries: they group by exactly the column they select. Postgres rejects a selected column that is neither grouped nor aggregated, unless you grouped by that table's primary key, and MySQL rejects it under ONLY_FULL_GROUP_BY, the default since MySQL 5.7. DuckDB and SQLite accept it and hand you an arbitrary value.

The Steam edition launches on Nov 12 with the whole course, holographic reward cards and a title-screen parallax. Wishlist on Steam