LeetCode SQL — Students and Examinations: cross join + LEFT JOIN с подсчётом

Условие

Таблицы:

• Students(student_id, student_name)
• Subjects(subject_name)
• Examinations(student_id, subject_name) — лог попыток сдать предмет (могут быть нули, могут быть много).

Для каждой пары (student, subject) верните количество посещений. Все пары должны быть в выводе — даже если 0 попыток. Сортировка: student_id, subject_name.

Решение

Подход — CROSS JOIN Students × Subjects, потом LEFT JOIN с экзаменами

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

Зачем CROSS JOIN

Если просто сделать JOIN Examinations, появятся только пары, где была хотя бы одна попытка. CROSS JOIN порождает все возможные комбинации student×subject, а LEFT JOIN с Examinations подтягивает счётчик (NULL → 0).

COUNT(e.subject_name) vs COUNT(*)

COUNT(*) посчитает строку с NULL как 1 — пары без попыток получат 1 вместо 0. COUNT(e.subject_name) игнорирует NULL — правильный результат.

Подводные камни

1. Забыли CROSS JOIN. Без него пары с 0 попыток теряются.
2. COUNT(*) на LEFT JOIN. Даст 1 для пар без матча — частая ошибка.
3. GROUP BY полей. В строгом ANSI SQL все не-агрегированные поля должны быть в GROUP BY. Postgres строгий, MySQL — нет.

Эталонный ответ

Students CROSS JOIN Subjects LEFT JOIN Examinations, COUNT(e.subject_name) (не *), GROUP BY студента и предмета, сортировка по обоим.