Как проверить команда является ли выражение null

NULL-значения в SQL

Достаточно часто встречаются такие случаи, когда в таблице имеются записи с не заданными значениями какого-либо из полей, потому что значение поля неизвестно или его просто нет. В таких случаях SQL позволяет указать в поле NULL-значение. Строго говоря, NULL-значение вовсе не представлено в поле. Когда значение поля есть NULL — это значит, что программа базы данных специальным образом помечает поле, как не содержащее какого-либо значения для данной строки (записи).

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

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

SQL IS NULL

Поскольку NULL фиксирует пропущенные значения, результат любого сравнения при наличии NULL-значений неизвестен. Когда NULL-значение сравнивается с любым значением, даже с NULL-значением, результат просто неизвестен. Булево значение «неизвестно» ведет себя также, как «ложь» — строка, на которой предикат принимает значение «неизвестно», не включается в результат запроса – при одном важном исключении: NOT от лжи есть истина (NOT (false)=true), тогда как NOT от неизвестного значения есть также неизвестное значение. Следовательно, такое выражение как «city = NULL» или «city IN (NULL)» является неизвестным независимо от значения city.

Часто необходимо различать false и unknown – строки, содержащие значения столбца, не удовлетворяющие предикату, и строки, которые содержат NULL. Для этой цели SQL располагает специальным оператором IS, который используется с ключевым словом NULL для локализации NULL-значения. SQL IS NULL. Пример. Вывести все поля из талицы Customers, значения поля city которых равны NULL:

SELECT * FROM Customers WHERE city IS NULL

В данном случае выходных данных не будет, поскольку в поле city нет NULL-значений.

SQL IS NOT NULL

Условие IS NOT NULL используется в запросах для выборки записей со значениями не равных значению NULL SQL IS NOT NULL. Пример. Вывести все поля из талицы Customers, значения поля city которых НЕ равны NULL:

IS NULL (Transact-SQL)

Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.

Аргументы

выражение
Любое допустимое выражение expression.

Логическое НЕ
Задает отрицание логического результата. Предикат меняет возвращаемые выражением значения на обратные, возвращая TRUE, если значение не равно NULL и FALSE, если значение равно NULL.

Типы результата

Boolean

Значения кода возврата

Если значение expression равно NULL, IS NULL возвращает TRUE; в противном случае возвращается значение FALSE.

Если значение expression равно NULL, IS NOT NULL возвращает FALSE; в противном случае возвращается значение TRUE.

Замечания

Для определения, имеет ли выражение значение NULL, используйте IS NULL или IS NOT NULL вместо сравнения операторов (например = или !=). Сравнение операторов возвращает UNKNOWN, если хотя бы один аргумент или они оба равны NULL.

Примеры

В следующем примере возвращается наименование и вес всех продуктов, для которых вес меньше 10 фунтов, или неизвестен цвет, либо NULL .

USE AdventureWorks2022; GO SELECT Name, Weight, Color FROM Production.Product WHERE Weight < 10.00 OR Color IS NULL ORDER BY Name; GO 

Примеры: Azure Synapse Analytics и система платформы аналитики (PDW)

В следующем примере возвращаются полные имена всех сотрудников с инициалами отчества.

-- Uses AdventureWorks SELECT FirstName, LastName, MiddleName FROM DIMEmployee WHERE MiddleName IS NOT NULL ORDER BY LastName DESC; 

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 в SQL: Что это такое и почему его знание необходимо каждому разработчику

NULL - это специальное значение, которое используется в SQL для обозначения отсутствия данных. Оно отличается от пустой строки или нулевого значения, так как NULL означает отсутствие какого-либо значения в ячейке таблицы.

История появления NULL в SQL довольно интересна и длинна. В начале 1970-х годов Д. Камерер (D. Chamberlin) и Р. Бойд (R. Boyce) предложили использовать реляционную модель для полной замены иерархических и сетевых моделей данных, которые были актуальны в то время. Полная замена предполагала возможность хранения значений NULL в таблицах структуры базы данных.

Первоначально, NULL был создан как интегральный элемент реляционной модели данных. Это означало, что NULL мог быть использован в качестве значения для любого типа данных (целого числа, строки и т.д.) или даже целой строки (например, таких значений как "неизвестно" или "нет данных").

Когда была разработана SQL, NULL был реализован как специальное значение или маркер, который указывает на отсутствие значения в столбце. Таким образом, в SQL NULL означает отсутствие значения или неопределенное значение.

Однако, NULL создал некоторые проблемы при работе с данными в SQL. Например, если вы выполняете операцию на столбце, содержащем NULL значение, результат операции также будет NULL. Это означает, что использование NULL может приводить к нежелательным результатам, таким как непредсказуемое поведение.

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

Рассмотрим несколько SQL операций с NULL:

1. Как назначить значение NULL в SQL?
   Значение NULL можно явно указать при создании таблицы или добавления записей в таблицу. Например, при создании таблицы можно указать, что один из столбцов не обязательно должен иметь значение, используя ключевое слово NULL.
2. Как проверить NULL в SQL?
   Для того чтобы проверить значение NULL в SQL, используется оператор IS NULL. Этот оператор возвращает true, если значение столбца равно NULL.
3. NULL и требования целостности данных
   NULL может нарушить требования целостности данных, которые гарантируют, что данные в таблице являются валидными и согласованными. Например, требование необходимости заполнения поля значением может быть нарушено, если значение NULL допустимо.
4. Ограничение NOT NULL
   Ограничение NOT NULL позволяет определить, что значение в столбце не может быть NULL. Это означает, что при добавлении записи в таблицу обязательно должно быть заполнено значение для данного столбца.
5. ISNULL
   Функция ISNULL возвращает первый аргумент, если он не равен NULL, и второй аргумент, если первый аргумент равен NULL. ISNULL наиболее часто используется для замены значений NULL на конкретные значения.
6. COALESCE
   Функция COALESCE возвращает первый аргумент, который не равняется NULL. COALESCE может быть полезна, когда вам нужно выбрать первое значение из двух или нескольких, которые могут быть пустыми.
7. NULLIF
   Функция NULLIF возвращает NULL, если два аргумента равны. Если аргументы не равны, она возвращает первый аргумент. Эта функция может быть полезна для условного выполнения некоторых операций в зависимости от того, равны ли значения.

Почему знание NULL важно для SQL-разработчиков?

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

NULL в базе данных может привести к ошибкам, например:

1. Сравнение значений. Если в таблице присутствуют значения NULL, то при выполнении операции сравнения, например, WHERE column_name = NULL, результатом будет False. Вместо этого нужно использовать оператор IS NULL.
2. Вычисления. Если при выполнении арифметических операций включены значения NULL, то результат такой операции тоже будет NULL. Например, 5 + NULL = NULL.
3. Сортировка. При сортировке значений в столбце, которые содержат NULL, может произойти непредсказуемый результат в зависимости от реализации сортировки в базе.
4. Внешние ключи. Если в таблице соединения используются внешние ключи, то значение NULL может привести к нарушению связной целостности.
5. Агрегирующие функции. При использовании агрегирующих функций в запросах, значения NULL могут не быть учтены в результате.
6. Вывод на экран. Если значение NULL выводится на экран пользователя, это может вызвать возможное недопонимание и ухудшение пользовательского опыта.

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

Пример неудачного использования NULL

Допустим, у нас есть таблица, в которой хранится информация о заказах в интернет-магазине. Среди полей есть поля, отражающие дату создания заказа (orderdate) и дату его доставки (deliverydate).

Однажды в этой таблице обнаружилась ошибка: у нескольких заказов deliverydate было не заполнено, т.е. им было присвоено значение NULL. Разработчики не заметили этого и продолжили работу с данными.

Однако при анализе статистики продаж на одном из графиков заказы отображались в зависимости от даты доставки. Из-за того, что несколько заказов не имели значения в поле deliverydate, они не отображались на графике вовсе, что привело к искажению реальных данных и ошибочным выводам о продажах на определенные даты.

Эта ошибка привела к тому, что команда интернет-магазина долго работала с неточными данными, и необходимо было потратить много времени на исправление ошибки и калибровку аналитических инструментов. Все эти проблемы могли быть исправлены, если бы разработчики были внимательными и не допустили присвоения значения NULL в поле, которое требует обязательного заполнения.

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

Бонус

Три вопроса с собеседований, где вас проверяют на знание NULL в SQL:

1. Как проверить, есть ли NULL значение в определенном столбце таблицы в SQL? Ответ: Необходимо использовать оператор "IS NULL" или "IS NOT NULL". Например, чтобы проверить, есть ли NULL значение в столбце "name" таблицы "users", нужно выполнить следующий запрос: SELECT FROM users WHERE name IS NULL;
2. Как можно заменить NULL значения на определенное значение в SQL? Ответ: Для замены NULL значений можно использовать оператор "COALESCE". Например, чтобы заменить NULL значения в столбце "price" таблицы "products" на значение 0, нужно выполнить следующий запрос: SELECT COALESCE(price, 0) FROM products;
3. Как можно проверить, что два столбца имеют одинаковые значения, включая NULL, в SQL? Ответ: Для этого нужно использовать оператор "IS NOT DISTINCT FROM". Он сравнивает значения двух столбцов, включая NULL значения. Например, чтобы проверить, что значения столбцов "name" и "address" в таблице "users" совпадают, нужно выполнить следующий запрос: SELECT FROM users WHERE name IS NOT DISTINCT FROM address;