SQL: проверяем JOIN, NULL и гранулярность
Исполняем запросы на маленьком наборе и сравниваем с заранее рассчитанным результатом.
- Сохранить пользователей с нулём публикаций при LEFT JOIN.
- Объяснить COUNT(*), COUNT(column) и COUNT(DISTINCT).
- Проверить предложенный SELECT на учебной SQLite-базе.
Сначала определите гранулярность
Задача: получить одну строку на каждого пользователя и число созданных им опубликованных задач. Пользователь без задач должен остаться с нулём. По OD-DATA-1 ожидается: Анна — 1, Борис — 1, Вера — 0. Ручной прогноз перед запуском помогает обнаружить ошибку, а не просто получить правдоподобную таблицу.
Ассистент предложил LEFT JOIN, но поставил t.state="published" в WHERE. У Веры поля присоединённой задачи равны NULL; фильтр не истинен, и строка пропадает. Синтаксис корректен, но задача не решена. Для данного ожидания фильтр состояния правой таблицы нужен в ON. Это следует из смысла внешнего соединения, а не из предпочтения стиля.
Полная учебная фикстура
-- OrbitDesk synthetic fixture OD-DATA-1. Use a NEW disposable SQLite database.
PRAGMA foreign_keys = ON;
CREATE TABLE users (id TEXT PRIMARY KEY, name TEXT NOT NULL, role TEXT NOT NULL);
CREATE TABLE tasks (id TEXT PRIMARY KEY, author_id TEXT NOT NULL REFERENCES users(id), title TEXT NOT NULL, state TEXT NOT NULL, assignee_id TEXT REFERENCES users(id));
CREATE TABLE comments (id TEXT PRIMARY KEY, task_id TEXT NOT NULL REFERENCES tasks(id), body TEXT NOT NULL);
INSERT INTO users VALUES ('u1','Анна','user'),('u2','Борис','user'),('u3','Вера','editor');
INSERT INTO tasks VALUES
('t1','u1','Проверить форму','published','u2'),
('t2','u1','Черновик','draft',NULL),
('t3','u2','SQL-проверка','published',NULL),
('t4','u2',' ','draft','u1');
INSERT INTO comments VALUES
('c1','t1','Проверено'),('c2','t1','Нужна правка'),
('c3','t3','Готово'),('c4','t2','Черновой комментарий');Корректный запрос для одной строки на пользователя
-- Q1: One row per user, including users with zero published tasks.
SELECT u.id, u.name, COUNT(t.id) AS published_count
FROM users AS u
LEFT JOIN tasks AS t
ON t.author_id = u.id AND t.state = 'published'
GROUP BY u.id, u.name
ORDER BY u.id;
-- Expected: u1|Анна|1 ; u2|Борис|1 ; u3|Вера|0Что считает агрегат
После LEFT JOIN пользователь без соответствия представлен одной строкой с NULL в полях задачи. COUNT(*) посчитает эту строку и вернёт 1. COUNT(t.id) посчитает только ненулевые ID и даст 0. Отличие незаметно, если у всех есть задачи; поэтому Вера является контрольным контрпримером.
Добавим комментарии. У t1 их два, и соединение пользователь → задача → комментарии создаёт две строки для одной t1. COUNT(t.id) теперь завысит число задач Анны до 2. Для этой метрики можно использовать COUNT(DISTINCT t.id) или предварительно агрегировать задачи. DISTINCT нельзя добавлять механически в любой запрос: сначала назовите сущность, которую нужно считать. Число комментариев и число задач — разные показатели.
NULL и сравнение с ожидаемыми ID
SELECT id FROM tasks WHERE assignee_id IS NULL ORDER BY id;
-- Ожидаются t2 и t3.
-- WHERE assignee_id = NULL не выбирает эти строки.Исполняем по шагам
Создайте новую одноразовую SQLite-базу. Загрузите фикстуру и проверьте контрольные объёмы: 3 пользователя, 4 задачи, 4 комментария. Сначала выполните соединение без агрегации и посмотрите, какие строки получились. Потом добавьте GROUP BY и COUNT. Сравните результат с прогнозом 1/1/0. Для проверяемого порядка используйте ORDER BY: без него порядок строк не является обещанием запроса.
Поиск неназначенных задач использует assignee_id IS NULL и должен вернуть t2 и t3. Выражение = NULL не делает ту же проверку: обычное сравнение с NULL не даёт истинного результата для этих строк. Разбор небольшого набора делает это наблюдаемым, а не формальным правилом для запоминания.
Границы безопасной проверки
Курс не просит подключаться к производственной базе. Даже SELECT может раскрыть запрещённые данные или создать нагрузку на рабочей системе. Здесь входы искусственные, схема мала и известна. Перед переносом предложенного ИИ запроса в работу уточните диалект, реальные связи и права. Правильный SQLite-запрос нельзя безусловно переносить в другую СУБД.
Сохраните запрос, версию фикстуры, ожидаемые и фактические строки. Объясните разницу с ошибочным вариантом. Отсутствие синтаксической ошибки подтверждает выполнимость, но не правильность выборки. В скачиваемом 02-queries.sql есть четыре проверенных контрольных запроса. Если SQLite недоступен, разберите соединённые строки вручную и честно назовите результат ручным разбором, а не выполнением СУБД.
ЗАДАЧА
Разбери SELECT относительно ожидаемой гранулярности.
КОНТЕКСТ
SQLite; одна строка на пользователя, включая ноль опубликованных задач.
ВХОДНЫЕ ДАННЫЕ
-- OrbitDesk synthetic fixture OD-DATA-1. Use a NEW disposable SQLite database.
PRAGMA foreign_keys = ON;
CREATE TABLE users (id TEXT PRIMARY KEY, name TEXT NOT NULL, role TEXT NOT NULL);
CREATE TABLE tasks (id TEXT PRIMARY KEY, author_id TEXT NOT NULL REFERENCES users(id), title TEXT NOT NULL, state TEXT NOT NULL, assignee_id TEXT REFERENCES users(id));
CREATE TABLE comments (id TEXT PRIMARY KEY, task_id TEXT NOT NULL REFERENCES tasks(id), body TEXT NOT NULL);
INSERT INTO users VALUES ('u1','Анна','user'),('u2','Борис','user'),('u3','Вера','editor');
INSERT INTO tasks VALUES
('t1','u1','Проверить форму','published','u2'),
('t2','u1','Черновик','draft',NULL),
('t3','u2','SQL-проверка','published',NULL),
('t4','u2',' ','draft','u1');
INSERT INTO comments VALUES
('c1','t1','Проверено'),('c2','t1','Нужна правка'),
('c3','t3','Готово'),('c4','t2','Черновой комментарий');
Проверяемый запрос:
SELECT u.id, COUNT(*) FROM users u LEFT JOIN tasks t ON t.author_id=u.id WHERE t.state='published' GROUP BY u.id;
Нужно получить u1=1,u2=1,u3=0, одну строку на каждого пользователя.
ОГРАНИЧЕНИЯ
Не изменяй данные и не подключайся к внешним системам. Проверь ON, WHERE, NULL, кратность JOIN и COUNT.
ОЖИДАЕМЫЙ ФОРМАТ
Логика, минимальный контрпример, исправленный SELECT, ожидаемая таблица и независимая проверка.Используйте разрешённый ИИ-сервис. Не отправляйте рабочие секреты и персональные данные.
Источники и проверка фактов
SQLite — SELECTПоведение LEFT JOIN и различие условий ON и WHERE для строк без соответствия.SQLite — встроенные агрегатные функцииCOUNT(*) считает строки, COUNT(column) — ненулевые значения; это важно после LEFT JOIN.SQLite — выражения и NULLСравнения с NULL и применение IS NULL; учебные запросы явно используют диалект SQLite.Авторский учебный материал · Редакция QA Orbit · версия 2026-10-28.1