JOIN и кардинальность: как не потерять и не размножить
Строим отчёт со всеми пользователями и обнаруживаем завышение количества после связи с комментариями.
- Выбирать INNER или LEFT JOIN по требуемому набору строк.
- Проверять количество строк на каждом шаге соединения.
- Сохранять нулевые группы и корректно считать уникальные задачи.
Что является ведущим списком
Требование отчёта звучит так: вывести всех пользователей и число созданных ими опубликованных задач, включая ноль. Ведущий список — users: именно он задаёт обязательные строки u1, u2, u3 и u4. Задачи присоединяются по author_id, а не по assignee_id. Фильтр state='published' относится к подходящим задачам; он находится в ON. Тогда отсутствие подходящей задачи не исключает самого пользователя.
Ручной оракул прост: u1 создала t1 и t5, u2 создал t3, у u3 есть только архивная t6, у u4 вообще нет задач. Итог 2,1,0,0. Внешнее соединение формирует для пользователей без подходящих задач строку с NULL в колонках tasks. Поэтому считать нужно COUNT(t.id), а не COUNT(*): последняя функция посчитала бы и такую техническую строку. Нулевой результат — часть требования, а не косметическое дополнение отчёта.
Q6 · Полный список пользователей
SELECT u.id, COUNT(t.id) AS published
FROM users u
LEFT JOIN tasks t
ON t.author_id=u.id AND t.state='published'
GROUP BY u.id
ORDER BY u.id;
-- u1 | 2
-- u2 | 1
-- u3 | 0
-- u4 | 0Ошибка с WHERE и ошибка с размножением
Если перенести t.state='published' в WHERE, строки с NULL не пройдут условие. Получатся только u1 и u2; отчёт станет отвечать на вопрос «кто имеет опубликованные задачи». Цифры у оставшихся людей будут правильными, поэтому проверка одного активного пользователя дефект не обнаружит. В fixture специально есть u3 с неподходящим состоянием и u4 без задач: они проверяют разные причины нуля.
Добавим комментарии, чтобы позже выводить их статистику. У t1 два комментария, у t3 два, у t5 один. После соединения одна задача уже не равна одной строке. У u1 строк три, хотя опубликованных задач две; у u2 строк две, хотя задача одна. Обычный COUNT(t.id) завысит результат до 3,2,0,0. DISTINCT по идентификатору задачи исправляет именно эту метрику. Он не является универсальным лекарством: если нужно суммировать оценки, SUM(DISTINCT estimate) может слить разные задачи с одинаковыми часами. Лучше заранее агрегировать дочерние данные до одной строки на задачу.
Q7 · Считаем задачи после связи один ко многим
SELECT u.id, COUNT(DISTINCT t.id) AS published
FROM users u
LEFT JOIN tasks t
ON t.author_id=u.id AND t.state='published'
LEFT JOIN comments c ON c.task_id=t.id
GROUP BY u.id
ORDER BY u.id;
-- u1 | 2; u2 | 1; u3 | 0; u4 | 0Проверка соединения по шагам
Для диагностики временно уберите агрегат и выведите u.id,t.id,c.id. Ожидаемые дочерние строки у u1: t1/c1, t1/c2, t5/c5. Это объясняет рост числителя без предположений о дублях в самой таблице tasks. В репорте различайте дубли сущности в хранилище и повтор представления этой сущности после JOIN. Первый требует проверки ключа и записи, второй может быть нормальной математикой связи.
Собирайте минимальный набор, где есть ноль, одна и несколько дочерних записей. Если в каждом примере ровно один комментарий, ошибочный COUNT успешно пройдёт проверку. Если отчёт включает автора и исполнителя одновременно, присоединяйте users дважды с разными псевдонимами и проверяйте каждую роль отдельно. Совпадение этих ролей на одной задаче не доказывает правильность связи: t1 с разными u1/u2 выступает обязательным контрпримером. Для нулевых комментариев t2, t4 и t6 также важно сохранить задачи в отчёте. Q10 считает COUNT(c.id) по каждой задаче и выдаёт 2,0,2,0,1,0. Любой неожиданный пропуск или лишняя строка должен быть объяснён соединением, а не скрыт произвольным DISTINCT всего результата.
Источники и проверка фактов
SQLite · JOIN processingВнешние соединения и расположение условий.SQLite · DISTINCT aggregatesИсключение повторов аргумента COUNT.Авторский учебный материал · Редакция QA Orbit · версия 2026-10-28.1