Почему запрос вернул меньше строк
Вы пишете условие, которое должно найти все строки с нужным значением. В таблице такие строки есть — вы их видите глазами. Но запрос возвращает меньше строк, чем ожидалось. Или не возвращает вовсе. Ни ошибки, ни предупреждения — просто выборка короче.
Вот три формулировки, с которыми приходят к этой теме:
- «Запрос вернул меньше строк, чем должно быть»;
- «NOT IN вернул пусто, хотя в таблице есть подходящие строки»;
- «Среднее по столбцу оказалось выше ожидаемого».
У всех трёх одна причина: в SQL сравнение с отсутствующим значением работает не так, как с обычным. Язык не сообщает об этом явно — он просто отбрасывает строки, которые не прошли условие.
Отсутствие значения — не значение
NULL — это не значение, а признак отсутствия значения. Когда вы сравниваете что-то с NULL через равенство или неравенство, результат не истина и не ложь, а неизвестность (UNKNOWN). Стандарт языка это прописывает, и стандартное поведение включено по умолчанию.
Запись = NULL синтаксически допустима, но при стандартном поведении даёт неизвестность. Чтобы проверить отсутствие значения, используют IS NULL и IS NOT NULL. В отдельных базах сохранился устаревший переключатель, при котором = NULL ведёт себя как IS NULL, но полагаться на него не стоит.
Конструкция WHERE пропускает строку дальше только при истине. Неизвестность отбрасывается так же, как ложь. Поэтому строки с NULL не попадают в выборку ни по условию = значение, ни по условию <> значение — оба дают неизвестность, а WHERE пропускает только истину.
Это не свойство NULL, а свойство конструкции WHERE. Ограничение CHECK работает по обратному правилу: строка проходит, если условие не ложно. Поэтому CHECK со сравнением по столбцу не мешает вставить в этот столбец NULL — сравнение даёт неизвестность, а неизвестность проверку проходит. Запрещает отсутствие значения ограничение NOT NULL — и та же проверка, если её написать явно как проверку на отсутствие значения. В CASE то же правило даёт другой исход: ветка WHEN, условие которой дало неизвестность, просто не выбирается, но строка при этом никуда не девается — CASE без ELSE, не нашедший ни одной ветки, возвращает NULL. Поэтому простая форма вида CASE столбец WHEN NULL не срабатывает никогда: под капотом это равенство.
Неизвестность не всегда отравляет условие
Неизвестность распространяется не всегда. В цепочке ИЛИ истина поглощает неизвестность: если хотя бы одно слагаемое истинно, результат истинен независимо от остальных. В цепочке И ложь поглощает неизвестность: если хотя бы одно слагаемое ложно, результат ложен.
Отрицание неизвестность не переворачивает: NOT от неизвестности остаётся неизвестностью — ни истиной, ни ложью она не становится. Поглощать тут нечего: отрицание применяется к одному условию, и второго слагаемого, которое перебило бы неизвестность, рядом нет. Поэтому добрать пропавшие строки, обернув условие в NOT, не выйдет: запрос «равно» и запрос «не равно» вместе не покрывают таблицу — строки с NULL не вернёт ни один; отсюда же растёт ловушка NOT IN.
Ловушка NOT IN
Конструкция NOT IN со списком или подзапросом, где встретился хотя бы один NULL, не вернёт ни одной строки. Условие раскрывается в цепочку неравенств, соединённых И. Эта цепочка не может дать истину: сравнение с отсутствующим значением истиной не бывает, а WHERE пропускает только истину. Поглощение из предыдущего раздела здесь не выручает: ложь обрывает цепочку в ложь, и строка точно так же не проходит.
IN безопасен не сам по себе, а по месту употребления. При найденном совпадении он даёт истину, но при отсутствии совпадения и наличии NULL в списке или подзапросе — неизвестность, а не ложь. Разница проявляется там, где неизвестность и ложь перестают быть взаимозаменяемыми: под отрицанием, внутри CASE, в ограничении CHECK, при записи результата в столбец логического типа.
Чтобы избежать ловушки, используют NOT EXISTS. Он спрашивает о наличии строк, а не о равенстве значений, поэтому даёт только истину или ложь и никогда неизвестность. Но NOT EXISTS — не механическая замена NOT IN: на строке, где NULL стоит в проверяемом внешнем столбце, NOT IN её отбросит, а NOT EXISTS вернёт.
Для сравнения двух столбцов, в каждом из которых может быть NULL, используют IS DISTINCT FROM и IS NOT DISTINCT FROM. Это стандартное сравнение, в котором два NULL считаются одинаковыми, а NULL и значение — разными. Результат всегда истина или ложь. Там, где такого оператора нет, тот же смысл пишут вручную: либо столбцы равны, либо оба пусты.
Агрегаты и NULL
Агрегаты SUM, AVG, MIN, MAX и COUNT по конкретному столбцу игнорируют NULL. COUNT(*) считает строки, COUNT(столбец) — только строки с заполненным значением. Среднее делит сумму на число заполненных строк, а не на общее число строк.
Сумма по пустому множеству возвращает одну строку со значением NULL, а счётчик строк по пустому множеству — ноль. С GROUP BY пустая группа не возвращает строки вовсе. Оборачивать её результат в COALESCE бесполезно — оборачивать нечего. Недостающие группы добираются внешним соединением со списком ключей.
COALESCE возвращает первый аргумент, отличный от NULL. Это подстановка в выводе, а не в данных: в выводе агрегата она законна; вокруг столбца превращается в тупик, и разбирается он ниже отдельно.
Три разных поведения NULL
У NULL три разных поведения, а не два:
- В сравнении два NULL не равны друг другу;
- При устранении дублей (GROUP BY, DISTINCT, операции над множествами, разбиение оконной функции) они считаются одним значением и попадают в одну группу;
- В ограничении уникальности они по стандарту считаются разными — потому и не конфликтуют между собой; зато именно здесь базы расходятся сильнее всего;
Это три отдельных правила, а не следствия одного. Поведение уникальности расходится между базами: в одних вставляется любое число строк с NULL в уникальном столбце, в других NULL считаются равными и проходит только одна такая строка. Первичный ключ NULL запрещает везде.
Чтобы проверить поведение своей базы, выполните такой запрос:
CREATE TABLE test_nulls (val INT UNIQUE);
INSERT INTO test_nulls VALUES (NULL);
INSERT INTO test_nulls VALUES (NULL); -- прошло или упало на уникальности?
SELECT COUNT(*) FROM test_nulls; -- сколько строк осталось
DROP TABLE test_nulls;Вторая вставка либо проходит, либо падает на ограничении уникальности — это и есть ответ про вашу базу. Если клиент оборвал транзакцию на ошибке, счётчик и удаление таблицы выполните отдельно.
Откуда берутся NULL
Почему при соединении строк становится меньше, разбиралось в Соединения таблиц — здесь важен только механизм. Внешнее соединение само создаёт NULL в столбцах неприсоединившейся стороны. Условие по этой стороне, перенесённое из ON в WHERE, превращает внешнее соединение во внутреннее, если оно отбрасывает NULL. Условие IS NULL по той же стороне, наоборот, оставляет именно неприсоединившиеся строки и даёт антисоединение — рабочий приём «найти то, чего нет».
Тупик: подставить вместо NULL значение
Первым делом столбец оборачивают в COALESCE с нулём или пустой строкой. Иногда даже правят данные разовым UPDATE по всей таблице. Сравнение действительно начинает работать — строки с NULL теперь проходят условие.
Но это не помогает:
- Подставленное значение теперь неотличимо от настоящего. Среднее будет делить на все строки, «ноль продаж» смешается с «данных не было»;
- Трёхзначная логика никуда не делась. В следующем запросе она всплывёт снова;
- Там, где пустая строка и так хранится как NULL, рецепт не срабатывает вовсе.
COALESCE вокруг результата агрегата (в выводе) и COALESCE вокруг столбца (в данных) — разные вещи. Первый легален, второй — нет.
Позиция редакции
Столбец должен допускать NULL только с одним смыслом: «значения нет и оно неизвестно». Если один и тот же NULL означает «ещё не заполнили», «неприменимо» и «отказались отвечать», чинить надо схему, а не запрос.
Случаи разводят отдельным признаком или справочником — почему столбец вообще получает три смысла, разбиралось в материале Нормализация: до какой степени и когда отступать, — а столбец делают NOT NULL. На живой таблице это не одно действие, а последовательность с блокировкой на проверке; как её выстроить, разобрано в материале Миграции базы без простоя: порядок, который работает.
Неверна эта позиция, если данные приходят из внешнего источника, который сам этих случаев не различает. Добавочный признак пришлось бы выдумать, а выдуманный признак хуже честного NULL. Тогда NULL оставляют и описывают его смысл в контракте входа — как это устроено, разобрано в материале Контракты данных: как перестать чинить отчёты.