Students and Examinations

You build the complete student×subject grid with a CROSS JOIN (every student paired with every subject, whether or not they sat the exam), LEFT JOIN the actual exam sittings onto that grid so absent pairs survive as rows with NULL columns, then count a column from the Examinations side — because COUNT(column) skips NULLs, a no-show collapses to exactly 0 while a student who sat the same exam three times counts as 3.

The problem

Three tables. Students(student_id, student_name) and Subjects(subject_name) are the dimensions; Examinations(student_id, subject_name) is a fact table with no primary key — one row per sitting, so the same (student, subject) pair can appear many times. Output one row for every student×subject combination with how many times that student sat that exam, including the zeros, ordered by student_id then subject_name.

The trap is the zeros. If you start from Examinations and group, the pairs nobody sat simply do not exist in the data, so they never appear in the output. The grid has to be manufactured first, independent of attendance.

The mechanism in three moves

1. Manufacture the grid. Students CROSS JOIN Subjects emits the Cartesian product — every student against every subject. This is the universe of rows we must report, and it exists whether or not a single exam was ever sat.
2. Attach reality, lossless. LEFT JOIN Examinations on both keys. Where a sitting exists, its columns fill in; where none exists, the grid row is kept and the Examinations columns come back NULL. Using an inner join here would silently delete the zero rows — the whole point of the question.
3. Count a nullable column, not the row. COUNT(e.student_id) tallies only non-NULL values within each group. A no-show group has one row whose e.student_id is NULL → count 0. A pair sat three times has three non-NULL rows → count 3.

The query

SELECT s.student_id,
       s.student_name,
       sub.subject_name,
       COUNT(e.student_id) AS attended_exams
FROM   Students s
       CROSS JOIN Subjects sub
       LEFT JOIN Examinations e
              ON s.student_id   = e.student_id
             AND sub.subject_name = e.subject_name
GROUP  BY s.student_id, s.student_name, sub.subject_name
ORDER  BY s.student_id, sub.subject_name;

Read the FROM clause as: build the grid (CROSS), keep every grid row (LEFT), then fold each grid cell into one output row (GROUP BY) counting the matches. The ON predicate must name both keys; matching on student_id alone would attach a student's Physics sitting to their Math grid cell too.

Worked trace on the real data

Students: Alice (1), Bob (2), Alex (6), John (13). Subjects: Math, Physics, Programming. Examinations sittings: Alice sat Math 3 times, Physics 2 times, Programming 1; Bob sat Math 1 and Programming 1 (never Physics); John sat each once; Alex sat nothing at all.

The CROSS JOIN produces 4×3 = 12 grid rows. After the LEFT JOIN and GROUP BY, each cell counts its matching sittings:

The ORDER BY student_id, subject_name then sorts Alex (6) ahead of John (13) numerically and alphabetizes subjects within each student. Note Alex's three zeros: he has no rows in Examinations at all, yet he gets a full set of grid cells because the grid was built before attendance was consulted.

Why the naive version is wrong

Swap in COUNT(*) and the zeros break:

-- WRONG for no-shows
COUNT(*) AS attended_exams

COUNT(*) counts rows in the group, not non-NULL values. After a LEFT JOIN, a no-show pair still occupies exactly one row (the grid row, padded with NULLs). So Bob/Physics and every Alex cell would return 1 instead of 0 — reporting an exam attendance that never happened. The fix is to count any column that is NULL precisely when there was no match, i.e. a column from the LEFT-joined (e) side. This NULL-skipping behaviour of COUNT(column) is the single load-bearing fact of the whole solution; it is why COUNT(e.student_id) and COUNT(*) diverge by exactly the no-shows.

Pitfalls

• Counting the wrong column. COUNT(*), or counting a column from s / sub (which are never NULL after the join), always returns at least 1 and silently inflates every no-show to 1.
• Inner join instead of LEFT. An inner join drops every unmatched grid row, so the zero-attendance pairs (all of Alex, Bob/Physics) vanish entirely — the output is short and the bug is easy to miss on small data.
• Half the ON predicate. Joining on student_id only (forgetting subject_name) cross-contaminates: a student's sittings in other subjects attach to this subject's cell, over-counting.
• Grouping by name only. If two students share a name and you GROUP BY student_name, subject_name, their counts merge. Group by the key (student_id) and carry student_name along; it is functionally dependent on the id, which is why MySQL tolerates it in the SELECT.
• ORDER BY omitted. Without an explicit ORDER BY, SQL gives no row-order guarantee; the grader expects (student_id, subject_name) order specifically.

Takeaways

• Build the grid before consulting the facts. CROSS JOIN of the dimension tables is how you guarantee a row for every combination, including ones with zero activity.
• LEFT JOIN preserves the grid; the join side determines what is NULL. Unmatched grid rows survive with NULLs in the fact columns — that NULL is the signal of "no activity".
• COUNT(column) ignores NULL; COUNT(*) does not. Counting a fact-side column turns no-shows into a clean 0; this is the entire trick.
• Group by keys, not display names, to avoid collisions, and always state the ORDER BY the spec asks for.

Problem from LeetCode 1280 "Students and Examinations". Mechanism cross-checked against the PostgreSQL documentation on aggregate functions (COUNT ignores NULL inputs) and the SQL standard's treatment of outer joins producing NULL-extended rows. Re-authored and deepened for this guide: the NULL-skipping insight behind COUNT(e.student_id) vs COUNT(*) is now named explicitly with a worked contrast, the Step 3 placeholder subquery was replaced with the real query and traced rows, and the "raws" typo was corrected.