NULL в SQL: Почему = NULL ничего не находит и что писать вместо этого

1. Что на самом деле означает NULL и почему = NULL ничего не находит

Перед пояснением: два билета в этой таблице не имеют правопреемника. Что вы ожидаете вернуть от этого запроса?

SELECT * FROM tickets WHERE assignee = NULL;

Ноль строк. Не два неназначенных билета. Ноль из десяти, каждый раз, в каждой базе данных. Я запустил его и получил пустой результат, а причина в самом определении NULL.

NULL не означает ноль и не означает пустой текст. NULL означает неизвестно: в этой строке есть слот правопреемника, и никто не сказал, что в нем происходит. Так assignee = NULL спрашивает: «Соответствует ли правопреемник этого билета неизвестному значению?», и единственный честный ответ: «Я не знаю». Даже для билета 4, где правопреемник сам имеет значение NULL, возникает вопрос: «равно ли одно неизвестное другому неизвестному?», и ответ все еще неизвестен. я побежал SELECT NULL = NULL и база данных возвращает NULL, что неверно.

Таким образом, SQL дает вам отдельный глагол для вопроса, который вы на самом деле имели в виду. IS NULL спрашивает: «Это значение отсутствует?», Это вопрос о слоте, а не о значении, поэтому он имеет реальный ответ «да» или «нет».

SELECT * FROM tickets WHERE assignee IS NULL;

Есть два неназначенных билета. IS NOT NULL является зеркалом и возвращает остальные 8 строк. Правило, которое следует соблюдать: = и <> сравнить значения, IS NULL и IS NOT NULL проверьте наличие отсутствующих, они не взаимозаменяемы.

2. Трехзначная логика в повседневных словах

Перед пояснением: на столе 10 билетов и 3 из них принадлежат Прие. Сколько строк занимает WHERE assignee <> 'Priya' возвращаться?

Большинство людей говорят, что семь. В базе данных указано пять. Я пробежал: 5 рядов.

Вот машина, простыми словами. В большинстве случаев на вопрос есть два ответа: да или нет. В SQL есть три сравнения: да, нет и неизвестно, где неизвестное — это то, что вы получаете всякий раз, когда используется NULL. Это называется трехзначной логикой, и имя имеет меньшее значение, чем последствия: WHERE сохраняет только те строки, которые отвечают «да». Строки с ответом «нет» удаляются, как и строки с ответом «неизвестно», автоматически, без ошибок и предупреждений.

Итак, для assignee <> 'Priya': пять билетов отвечают «да» (Маркуса и Даны), три отвечают «нет» (прии), а билеты 4 и 7 отвечают неизвестно, потому что сравнение NULL с «Прией» не дает честного ответа. Неизвестные исчезают. Десять вошли, пять вышли, а два неназначенных билета не находятся ни в стопке «Прия», ни в стопке «Не-прия».

Прежде чем читать дальше, скажите вслух, где находятся билеты 4 и 7, потому что в этом предложении и заключается вся хитрость. Они не были исключены из-за того, что принадлежали Прие. Они были исключены из-за отсутствия ответа. Когда вы действительно имеете в виду «всех, кроме Прии, включая неназначенных», вы должны так и сказать.

SELECT COUNT(*) AS n
FROM tickets
WHERE assignee <> 'Priya' OR assignee IS NULL;
-- n = 7

3. Ловушка NOT IN: один NULL, ноль строк.

Прежде чем объяснить: у вас есть список сотрудников, которые закрывают заявки, и вы хотите, чтобы заявки обрабатывал кто-то, кого нет в списке. В списке три записи: Прия, Маркус и одна NULL из-за неправильного импорта. Как вы думаете, сколько строк вернется?

SELECT * FROM tickets
WHERE assignee NOT IN (SELECT name FROM closers);

Ноль строк. Не «билеты Даны», не «все, кроме Прии и Маркуса». Ноль, и я запустил его, чтобы подтвердить. Это самый неприятный NULL-сюрприз в SQL, поскольку запрос выглядит вполне разумным и возвращает пустой набор без ошибок.

Причина следует из второго шага. NOT IN разворачивается в цепочку сравнений: assignee <> 'Priya' AND assignee <> 'Marcus' AND assignee <> NULL. Последнее сравнение дает неизвестный ответ для каждой строки таблицы. А цепочка операторов AND может ответить «да» только в том случае, если каждая ссылка отвечает «да», поэтому лучшее, что может сделать любая строка, неизвестно. Ни одна строка не отвечает да, WHERE сохраняет только да, а результат пустой. Один NULL в списке отравляет все десять строк.

Два исправления, оба я выполнил. Отфильтруйте NULL из подзапроса, который возвращает 2 билета Дана:

SELECT ticket_id, assignee
FROM tickets
WHERE assignee NOT IN (
    SELECT name FROM closers WHERE name IS NOT NULL
);
-- tickets 6 and 8, both Dana

Или используйте NOT EXISTS, который проверяет по одной строке «нет соответствующей строки» и невосприимчив к ловушке. Он вернул 4 строки одних и тех же данных: два билета Даны плюс два неназначенных, поскольку неназначенный билет действительно не имеет совпадений в списке. Эти два исправления расходятся во мнениях относительно билетов 4 и 7, и ни одно из них не является ошибочным. Они отвечают на разные вопросы, поэтому развилка такова: должны ли неназначенные билеты считаться «нет в списке»? Если да, NOT EXISTS говорит то, что вы имеете в виду. Если нет, экранированный NOT IN делает. Решающее значение имеет предложение, которое вы бы сказали заинтересованной стороне, и стоит вписать это предложение в запрос в качестве комментария.

4. COALESCE для значений по умолчанию и когда его не следует использовать

Перед пояснением: отчет о вашей заявке поступает к менеджеру, который постоянно спрашивает, что означают пустые ячейки «правопреемник». Что бы вы хотели, чтобы эти клетки говорили вместо этого?

COALESCE это инструмент. Она принимает список значений и возвращает первое из них, отличное от NULL, что делает ее функцией заполнения пробелов: COALESCE(assignee, 'Unassigned') означает «правопреемник» или слово «Неназначенный», если его нет.

SELECT ticket_id,
       COALESCE(assignee, 'Unassigned') AS assignee_display
FROM tickets
ORDER BY ticket_id;

Я запустил его: на билетах 4 и 7 теперь написано «Не назначено», а имена остальных восьми показаны без изменений. Это честное использование COALESCE: маркировка отсутствий, чтобы читатель мог их увидеть.

Нечестное использование скрывает это. COALESCE(minutes_to_close, 0) заставляет NULL превращаться в нули, а время закрытия в нулевую минуту — это утверждение, а не метка: оно говорит, что билет закрыт мгновенно, хотя на самом деле никто его не записал. Три из десяти билетов здесь не имеют близкого времени. Это 30%-ная дыра в столбце, и дыра такого размера — это открытие, о котором ваш читатель заслуживает услышать, а не проблема форматирования, которую нужно сгладить. Когда вы заполняете значение для отображения, укажите это в отчете, а если пропущенное значение велико, сообщите об этом как в отдельной строке. Документирование ограничений данных рассказывает о том, как это написать, не скрывая анализ.

5. Как значения NULL изменяют ваши значения и средние значения

Перед объяснением: minutes_to_close имеет семь действительных значений и три NULL. Когда ты принимаешь AVG(minutes_to_close)на какое число делит база данных: на десять или на семь?

Семь. Агрегатные функции полностью пропускают NULL: SUM, AVG, MIN, MAX, и COUNT(column) все действуют только на те строки, где существует значение. Я пробежал числа по обработанной таблице.

SELECT COUNT(*)                    AS all_rows,
       COUNT(minutes_to_close)     AS with_minutes,
       AVG(minutes_to_close)       AS avg_minutes
FROM tickets;

Среднее значение составляет 435, разделенное на 7, что составляет 62,1 минуты. Принудительно обнулить NULL с помощью COALESCE и в том же столбце среднее значение составляет 43,5, потому что теперь это 435, разделенное на 10. Это 30-процентное колебание в цифре заголовка из-за одного решения о недостающих данных, и ни одно из чисел не является автоматически правильным. 62,1 — это среднее значение билетов, время закрытия которых было зафиксировано. 43.5 притворяется, что незаписано, значит мгновенно. В честном отчете указано первое число и дыра: «В среднем 62,1 минуты по 7 из 10 билетов с зафиксированным временем закрытия».

COUNT(*) против COUNT(column) это тот же пропуск в форме счета. COUNT(*) считает строки, здесь 10. COUNT(minutes_to_close) подсчитывает значения, отличные от NULL, здесь 7, и COUNT(assignee) дает 8. Разрыв между двумя значениями — это количество пропущенных данных, что делает эту пару самой быстрой проверкой полноты в SQL. Вся семья COUNT, включая DISTINCT, находится в СЧЕТ в SQL.

Теперь картинка работает COUNT(*) рядом с COUNT(the_column) в самом важном числовом столбце ваших собственных данных. Знаете ли вы прямо сейчас, какой будет разрыв? Большинство людей узнают об этом позже, чем им хотелось бы.

6. Полное до и после

Та же таблица, тот же вопрос: как быстро каждый регион закрывает заявки?

До

SELECT region, AVG(minutes_to_close) AS avg_minutes
FROM tickets
GROUP BY region;

Три тихие проблемы, никаких ошибок. Среднее значение молча пропускает три незарегистрированных момента закрытия, поэтому никто не может увидеть, какой объем данных стоит за каждым числом. Два билета без указания региона образуют отдельную пустую группу без каких-либо объяснений. И читатель не имеет возможности узнать об этом из вывода.

после

-- ============================================================
-- STEP 4: How fast does each region close tickets?
-- WHY: minutes_to_close is missing on 3 of 10 tickets, and
--      region is missing on 2. Averages skip the NULLs, so
--      each average is shown WITH how many tickets back it.
-- NOTE: 'No region' is real missing data, reported as its
--      own line rather than dropped or blank.
-- ============================================================
--SELECT the region (labelled when missing), tickets in the
--   group, how many have a recorded close time, and their avg
SELECT COALESCE(region, 'No region')  AS region_display,
       COUNT(*)                        AS tickets,
       COUNT(minutes_to_close)         AS with_close_time,
       ROUND(AVG(minutes_to_close), 1) AS avg_minutes
FROM tickets
GROUP BY region;

Пустая группа теперь имеет имя, каждое среднее значение находится рядом с количеством значений, которые его создали, а отсутствие отображается вместо окружающего. Формат комментариев тот же, что и у Как комментировать SQL, чтобы он обучал. Чтобы передать эту форму пальцам, введите запрос самостоятельно в SQL-практикум, а не вставлять его.

7. Пограничные случаи: группы, соединения и порядок сортировки

Перед объяснением: NULL никогда не равен NULL, однако два билета без региона только что попали в одну группу вместе. Как и то, и другое может быть правдой?

Потому что разные части SQL намеренно обрабатывают NULL по-разному, и в этих швах живут сюрпризы. Четыре, чтобы знать.

GROUP BY помещает все NULL в одну группу. При группировке задается вопрос «какие строки принадлежат друг другу?», а стандартный ответ отвечает, что все пропущенные значения принадлежат друг другу, даже если среди них нет двух одинаковых. В рабочей таблице группировка по регионам возвращает Запад 4, Восток 4 и группу NULL, равную 2, что я и проверил, чтобы подтвердить. Эта пустая на вид строка не является сбоем. Это ваш счетчик недостающих регионов, и его стоит читать каждый раз.

LEFT JOIN создает NULL. LEFT JOIN сохраняет каждую строку из левой таблицы, а когда строка не находит совпадений в правой, правые столбцы получают значение NULL. Присоединив билеты к двухстрочному поиску регионов, в котором указан только Восток, я запустил его: четыре билета Востока получают своего менеджера, а остальные шесть строк показывают NULL, охватывая как настоящие билеты Запада без записи поиска, так и билеты, регион которых уже отсутствовал. Таким образом, NULL после соединения означает «нет совпадения или отсутствующий ключ», и их разделение требует IS NULL проверьте ключ соединения. SQL-СОЕДИНЕНИЯ гуляет всей семьей.

ORDER BY должен где-то помещать NULL, а базы данных с этим не согласны. Сортировка обработанной таблицы по minutes_to_close при возрастании в SQLite сначала помещаются три строки NULL, а затем 15; спуск ставит их на последнее место. SQLite и SQL Server рассматривают NULL как наименьшие, PostgreSQL и Оракул рассматривают их как самые большие, а PostgreSQL и Оракул принимают явные значения. NULLS FIRST или NULLS LAST сказать то, что вы имеете в виду. Переносимая привычка: когда отсортированный отчет имеет значение, укажите его место либо с помощью этого пункта, либо путем сортировки по the_column IS NULL первый.

Пустой текст не имеет значения NULL, за исключением случаев, когда он есть. Пустая строка — это известное значение: кто-то сказал «ничего», а не никто ничего не сказал. В большинстве баз данных фильтры и счетчики обрабатывают эти два значения по-разному, поэтому столбец может выглядеть пустым, пока IS NULL ничего не находит. Оракул — известное исключение, сохраняющее пустые строки как NULL. Если пробелы ведут себя странно, проверьте оба: WHERE col IS NULL OR col = ''.