SQL-Ex blog
Правильный способ проверки на NULL в запросах SQL Server
Добавил Sergey Moiseenko on Среда, 8 июня. 2022
Это все еще одна из наиболее общих проблем, которую я вижу в запросах.
Люди боятся NULL. Люди боятся сливаться на автострадах в Лос-Анджелесе.
В результате имеем бесконечный поток запросов с плохой производительностью, и некоторые неожиданные ошибки, обнаруживаемые по пути.
Я не могу ничего больше добавить в этом введении. Короче говоря, вы должны использовать естественные выражения типа IS NULL или IS NOT NULL, а не какие-либо встроенные функции, доступные вам в SQL Server, например, ISNULL, COALESCE и т.п., которые являются функциями слоя представления и не имеют реляционного смысла.
Отсюда и далее мы будем называть их неестественными выражениями. Возможно, так вам будет понятней.
Мастер настройки
Сначала о том, что я уже говорил ранее, но при использовании неестественных выражений оптимизатор не предоставит вам обратной связи о полезных индексах.
Первый запрос создает предложение по отсутствующему индексу, а второй — нет. Оптимизатор отказался от всякой надежды при использовании неестественного выражения.
Вторая проблема неестественных выражений связана с неявным преобразованием.
DECLARE
@i int = 0;
SELECT
c =
CASE ISNULL(@i, '')
WHEN ''
THEN 1
ELSE 0
END;
Будет возвращена 1, поскольку 0 и » могут быть неявно конвертированы.
Вот менее очевидный и более редкий пример:
DECLARE
@d datetime = '19000101';
SELECT
c =
CASE ISNULL(@d, '')
WHEN ''
THEN 1
ELSE 0
END;
Который тоже вернет 1.
Не много баз данных содержат данные, восходящие к 1900, но я вижу людей, довольно часто использующих это в качестве точки отсчета.
Если этого вам недостаточно, чтобы избавиться от такой идеи, давайте посмотрим, как все это происходит в реальном мире.
Давайте сначала создадим индекс. Без этого не будет фундаментальной разницы в производительности.
CREATE INDEX v ON dbo.Votes
(BountyAmount);
Нашим золотым стандартом будут эти два запроса:
SELECT
c = COUNT_BIG(*)
FROM dbo.Votes AS v
WHERE v.BountyAmount IS NULL;
SELECT
c = COUNT_BIG(*)
FROM dbo.Votes AS v
WHERE v.BountyAmount IS NOT NULL;
Первый запрос, который проверяет на значения NULL возвращает число 182,348,084.
Второй, который проверяет значения NOT NULL возвращает число 344,070.
Планы обоих запросов выглядят так:
Запросы выполняются соответственно за 846мс и 26мс. Очевидно, что запрос с более селективным предикатом будет здесь иметь преимущество по времени.
Неправильно
Здесь начинаются ошибки.
Этот запрос возвращает неправильные результаты, но вы, вероятно, привыкли к этому из-за всех этих хинтов NOLOCK в ваших запросах.
SELECT
c = COUNT_BIG(*)
FROM dbo.Votes AS v
WHERE ISNULL(v.BountyAmount, '') = '';
Возвращаемое количество 182349088, а не 182348084, поскольку для 1004 строк bounty равно 0.
Хотя мы использовали пустую строку в наших запросах, она неявно конвертировалась в 0.
И ты думал, что такой умный.
Плохо
В бесполезных упражнениях, которые выполняют люди, я часто наблюдаю использование выражений ISNULL, COALESCE и CASE.
Здесь стоит заметить, что COALESCE — это всего лишь стоящее за кадром выражение CASE.
Для нахождения NULL люди извернутся и сделают это:
SELECT
c = COUNT_BIG(*)
FROM dbo.Votes AS v
WHERE ISNULL(v.BountyAmount, -1) = -1;
SELECT
c = COUNT_BIG(*)
FROM dbo.Votes AS v
WHERE COALESCE(v.BountyAmount, -1) = -1;
Мы можем использовать здесь значение -1, поскольку оно не встречается в естественных данных. Результаты корректны в обоих случаях, но сравнительная производительность ужасна.
Мы видим 2,5 секунды против 900мс. Ситуация также становится хуже при более селективных предикатах.
Они оба занимают примерно одинаковое время, что и другие неестественные формы этого запроса, но вызов естественной версии этого запроса завершится менее чем за 30мс.
Я надеюсь, что мне не придется об этом больше писать, но сейчас я вижу, как люди делают это, и уже начинаю в этом сомневаться.
Я не знаю, почему некоторые думают, что это хорошая идея. Ходят слухи, что это исходит от разработчиков приложений, которые привыкли к значениям NULL, вызывающим ошибки при написании SQL-запросов, в которых они не представляют такой же угрозы.
Кто знает. Может быть, людям просто нравится праздничный розовый цвет текста, которым окрашиваются функции в SSMS.
Обратные ссылки
Нет обратных ссылок
Комментарии
Показывать комментарии Как список | Древовидной структурой
Автор не разрешил комментировать эту запись
Как проверить команда является ли выражение null
Выборка нулевых значений
После создания таблицы разработчик может указать, допустимо ли, чтобы в отдельных ее столбцах не содержались никакие значения. Когда в столбце не содержится никакого значения, это значит, что в нем содержится значение NULL.
Значение NULL подразумевает отсутствие какого-либо значения, в отличие от поля, содержащего или 0, или пустую строку, или просто несколько пробелов.
Для оператора SELECT предусмотрена специальная форма предложения WHERE, которая используется для проверки значений NULL в столбцах и содержит оператор IS NULL.
Синтаксис выглядит следующим образом:
Оператор IS NOT NULL позволяет выбрать строки, для которых значения в данном поле определены.
Найти номера расторгнутых договоров. (Если договор был расторгнут, то в поле retire_date содержится дата расторжения договора, для действующих договоров данное поле содержит значение NULL).
SQL:
SELECT contract_id
FROM tbl_contract
WHERE retire_date IS NOT NULL
SQL условие IS NOT NULL
В этом учебном материале вы узнаете, как использовать SQL условие IS NOT NULL с синтаксисом и примерами.
Описание
Условие IS NOT NULL используется в SQL для проверки значения, отличного от NULL. Оно возвращает TRUE, если найдено ненулевое значение, в противном случае оно возвращает FALSE. Его можно использовать в операторе SELECT, INSERT, UPDATE или DELETE.
Синтаксис
Синтаксис для условия IS NOT NULL в SQL:
expression IS NOT NULL
Параметры или аргументы
expression Выражение для проверки значения NOT NULL.
Пример — использование IS NOT NULL с оператором SELECT
При проверке значения, отличного от NULL, IS NOT NULL является рекомендуемым оператором сравнения для использования в SQL. Давайте начнем с примера, который показывает, как использовать условие IS NOT NULL в SELECT предложении.
В этом примере у нас есть таблица products со следующими данными:
| product_id | product_name | category_id |
|---|---|---|
| 1 | Pear | 50 |
| 2 | Banana | 50 |
| 3 | Orange | 50 |
| 4 | Apple | 50 |
| 5 | Bread | 75 |
| 6 | Sliced Ham | 25 |
| 7 | Kleenex | NULL |
Введите следующий SQL оператор:
FROM products
WHERE category_id IS NOT NULL ;
Будет выбрано 6 записей. Вот результаты, которые вы должны получить:
| product_id | product_name | category_id |
|---|---|---|
| 1 | Pear | 50 |
| 2 | Banana | 50 |
| 3 | Orange | 50 |
| 4 | Apple | 50 |
| 5 | Bread | 75 |
| 6 | Sliced Ham | 25 |
В этом примере будут возвращены все записи из таблицы products , где customer_id не содержит значения NULL.
Пример — использование IS NOT NULL с оператором UPDATE
Далее давайте рассмотрим пример использования условия IS NOT NULL в запросе UPDATE.
В этом примере у нас есть таблица customer со следующими данными:
| customer_id | first_name | last_name | favorite_website |
|---|---|---|---|
| 4000 | Justin | Bieber | google.com |
| 5000 | Selena | Gomez | bing.com |
| 6000 | Mila | Kunis | yahoo.com |
| 7000 | Tom | Cruise | oracle.com |
| 8000 | Johnny | Depp | NULL |
| 9000 | Russell | Crowe | google.com |
Введите следующий запрос UPDATE:
UPDATE customers
SET favorite_website = ‘google.com’
WHERE favorite_website IS NOT NULL ;
Будет обновлено 5 записей. Выберите данные из таблицы customer еще раз:
FROM customers;
Вот результаты, которые вы должны получить:
| customer_id | first_name | last_name | favorite_website |
|---|---|---|---|
| 4000 | Justin | Bieber | google.com |
| 5000 | Selena | Gomez | google.com |
| 6000 | Mila | Kunis | google.com |
| 7000 | Tom | Cruise | google.com |
| 8000 | Johnny | Depp | NULL |
| 9000 | Russell | Crowe | google.com |
В этом примере будут обновлены все значения fav_website в таблице customer до google.com, где favourite_website содержит значение NULL. Как вы видите, значения поля favorite_website обновлены все строки кроме одной.
Пример — использование IS NOT NULL с оператором DELETE
Далее давайте рассмотрим пример использования условия IS NULL в запросе DELETE.
В этом примере у нас есть таблица orders и следующими данными:
| order_id | customer_id | order_date |
|---|---|---|
| 1 | 7000 | 2019/06/18 |
| 2 | 5000 | 2019/06/18 |
| 3 | 8000 | 2019/06/19 |
| 4 | 4000 | 2019/06/20 |
| 5 | NULL | 2019/07/01 |
Введите следующий запрос DELETE:
Как проверить команда является ли выражение null
Концепция NULL -значения часто вводит в заблуждение новичков в SQL, которые считают, что NULL — то же, что и пустая строка «» . Это ошибка! Например, следующие команды совершенно различны:
mysql> INSERT INTO my_table (phone) VALUES (NULL); mysql> INSERT INTO my_table (phone) VALUES ("");
Обе команды вставляют значение в столбец phone , но первая — значение NULL , а вторая — пустую строку. Смысл первого можно передать как «номер телефона неизвестен», смысл второго — «у нее нет телефона».
В SQL сравнение значения NULL с любым другим значением, даже со значением NULL , всегда ложно. Выражение, содержащее NULL , всегда дает значение NULL , за исключением случаев, специально оговоренных в документации по операторам и функциям, присутствующим в выражении. Все столбцы в следующем примере возвращают NULL :
mysql> SELECT NULL,1+NULL,CONCAT('Invisible',NULL);
Если в столбце нужно найти значения NULL , то нельзя использовать условие =NULL . Следующая команда не возвращает ни одной строки, поскольку для любого выражения expr = NULL ЛОЖНО:
mysql> SELECT * FROM my_table WHERE phone = NULL;
Для поиска значений NULL необходимо использовать проверку IS NULL . Ниже показано, как найти телефонный номер NULL и пустой телефонный номер:
mysql> SELECT * FROM my_table WHERE phone IS NULL; mysql> SELECT * FROM my_table WHERE phone = "";
Заметим, что добавлять индекс по столбцу, в котором допускаются значения NULL , можно только в случае, если вы работаете с версией MySQL 3.23.2 или выше, а используемый тип таблиц — MyISAM или InnoDB . В более ранних версиях или для других типов таблиц необходимо объявлять такие столбцы с атрибутом NOT NULL . Это также подразумевает, что тогда нельзя вставлять NULL в индексированный столбец.
При чтении данных с помощью LOAD DATA INFILE пустые поля обновляются значениями ». Если необходимо поместить в столбец значение NULL, то в текстовом файле следует использовать \N . Также при некоторых обстоятельствах можно использовать слово-литерал NULL (see section 6.4.9 Синтаксис оператора LOAD DATA INFILE ).
При использовании ORDER BY значения NULL выдаются первыми. При сортировке в убывающем порядке с помощью DESC значения NULL также выдаются первыми. При использовании GROUP BY все значения NULL считаются равными.
Для обработки NULL предназначены операторы IS NULL и IS NOT NULL , а также функция IFNULL() .
Для некоторых типов столбцов значения NULL обрабатываются специальным образом. Если NULL вставляется в первый в таблице столбец типа TIMESTAMP , то в него помещается значение текущей даты и времени. При вставке NULL в AUTO_INCREMENT -столбец вставляется следующее число последовательности.