COUNT(*) против COUNT(col) против COUNT(DISTINCT)

Три записи count отличаются парой символов, а на собеседовании ими проверяют, понимаешь ли ты NULL. Вопрос короткий, ответ тоже, но кандидаты часто путаются, когда к нему добавляют LEFT JOIN. Запросы урока работают на учебной базе блока.

Как спрашивают

Чем отличаются count(*), count(manager_id) и count(DISTINCT manager_id)? Почему отдел без сотрудников показывает 1 вместо 0?

Короткий ответ

count(*) считает строки, неважно, что в них лежит. count(col) считает строки, где col не NULL. count(DISTINCT col) считает разные значения col, тоже без NULL. В запросе с LEFT JOIN у отдела без сотрудников всё равно есть одна строка, где колонки сотрудника NULL, поэтому count(*) даёт 1, а count(e.id) честный 0.

Разбор: журнал посещений

Представь классный журнал. count(*) это число строк в журнале. count(телефон) это число учеников, у которых телефон записан. count(DISTINCT класс) это сколько разных классов встречается в журнале. Пустые клетки во втором и третьем случае не считаются: о них ничего не известно.

SELECT count(*)                      AS all_rows,
       count(department_id)          AS with_dept,
       count(manager_id)             AS with_manager,
       count(DISTINCT department_id) AS departments,
       count(DISTINCT manager_id)    AS managers
FROM employees;

all_rows | with_dept | with_manager | departments | managers
---------+-----------+--------------+-------------+---------
       7 |         6 |            4 |           3 |        2

Разберём каждое число:

• all_rows = 7: в таблице семь сотрудников.
• with_dept = 6: у Жанны отдела нет, её NULL не считается.
• with_manager = 4: у Анны, Глеба и Егора руководителя нет.
• departments = 3: отделы 1, 2 и 3. NULL Жанны не считается отдельным значением.
• managers = 2: руководители встречаются двое, Анна (id 1) и Глеб (id 4), хотя ссылок на них четыре.

LEFT JOIN и лишняя единица

Классическая ловушка. Посчитаем сотрудников в каждом отделе двумя способами:

SELECT d.name,
       count(*)    AS star,
       count(e.id) AS by_id
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
GROUP BY d.name
ORDER BY d.name;

name       | star | by_id
-----------+------+------
Маркетинг  |    1 |     0
Поддержка  |    1 |     1
Продажи    |    2 |     2
Разработка |    3 |     3

В «Маркетинге» никого нет, но LEFT JOIN сохраняет отдел и дописывает к нему строку, где все колонки employees равны NULL. count(*) честно считает эту строку. count(e.id) её пропускает, потому что e.id там NULL. Правило: в запросе с LEFT JOIN считай колонку правой таблицы, лучше её первичный ключ, он не бывает NULL у настоящих строк.