NULL-safe сравнение в SQL или как избежать ловушек NULL при сравнении в SQL.

Введение

Если вы имеете опыт написания запросов на языке SQL, то вы конечно должны знать об особенностях NULL значений. На всякий случай напомню: NULL в SQL обозначает отсутствие значения в ячейке таблицы. NULL может быть использован в ячейке любого типа, если это не запрещено ограничением NOT NULL наложенным на столбец.

Особенности сравнения NULL

Важным моментом котрый должен знать любой разработчик SQL это то как ведет себя NULL в сравнении с любыми другими значениями. Так вот любое сравнение вида =, >, <, <>, !=, <=, >= с NULL вернёт NULL. Попробуйте сами

SELECT 2 = NULL;     -- NULL
SELECT NULL <> 2;    -- NULL 
SELECT NULL > 2;     -- NULL 
SELECT 'a' > NULL;   -- NULL
SELECT NULL = NULL;  -- NULL
SELECT NULL != NULL; -- NULL

На первый взгяд кажется странным, но если вспомнить что NULL это эквивалент значения "неизвестно" то всё встаёт на свои места!

Число 2 равно неизвестному значению? - неизвестно может да, а может и нет.
Неизвестное значение больше двух? - да кто ж его знает!
Может обно неизвесное значение равно другому? - понятия не имею!

Вот так могут выглядеть эти запросы в переводе на русский.

В связи с этой неопределенностью до недавнего времени SQL мог однозначно ответить только на два вопроса относительно NULL: знаем мы значение в ячейке или нет.

Для этого были введены специальные операторы IS NULL и IS NOT NULL

SELECT 2 IS NULL;       -- False
SELECT 'a' IS NOT NULL; -- True
SELECT NULL IS NULL;    -- True

Такое поведение создавало много трудностей в написании запросов. Например в таблице сотрудников компании EMPLOYEES есть колонка DEPARTRMENT с указанием подразделения в котором работает каждый из них. Представим нам нужно найти всех сотрудников отдела кадров (HR) Это довольно просто:

SELECT * FROM EMPLOYEES WHERE DEPARTMENT = 'HR';

А теперь обратная задача - выбрать всех КРОМЕ отдела кадров. Просто! - скажете вы и напишете так:

SELECT * FROM EMPLOYEES WHERE DEPARTMENT <> 'HR';

И.. потеряете запись о генеральном директоре, который не относится ни к одному из подразделений и значение DEPARTMENT в его строке - NULL! Так и до увольнения недалеко!

Как избежать таких проблем? До недавних пор проходилось всегда учитывать возможность NULL значений при помощи дополнинетьных условий типа:

SELECT * FROM EMPLOYEES WHERE DEPARTMENT <> 'HR' OR DEPARTMENT IS NULL;

Либо конвертировать NULL значения к значениям годным для сравнений:

SELECT * FROM EMPLOYEES WHERE COALESCE(DEPARTMENT, '') <> 'HR';

Я не буду объяснять работу оператора COALESCE - найдёте и прочитаете сами. Но думаю вам понятно что возможность появления NULL добавляет некоторое количество головной боли разработчику.

NULL-безопасное сравнение

Что же сделали разработчики PostgreSQL чтобы облегчить нам, простым SQL-писателям жизнь? Придумывают NULL SAFE оператор! Который позволяет безопасно производить сравнения не опасаясь что NULL значения испортят результат!

Итак - оператор IS DISTINCT / IS NOT DISTICT позволяет решать задачу сравнения следующим запросом:

SELECT * FROM EMPLOYEES WHERE DEPARTMENT IS DISTINCT FROM 'HR';

В переводе на русский это значит - Выбери все записи где отдел отличечтся от 'HR'. На мой взгляд очень красивая конструкция достойная того чтобы знать её и применять. К сожалению не все СУБД поддерживают такую конструкцию. Например MySQL и MariaDB в принципе имеют NULL SAFE сравнение, но используют другой синтаксис:

SELECT * FROM EMPLOYEES WHERE DEPARTMENT <=> 'HR';

А остальные СУБД пока не ввели данный оператов в своих базах данных.

Заключение

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

P.S.

Изучение SQL – это непрерывный процесс. Понимание нюансов работы с NULL и использование таких операторов, как IS DISTINCT FROM, значительно улучшает качество ваших запросов и делает вас более уверенным пользователем SQL.

Хотите углубить свои знания? Отличный ресурс для практики и обучения – SQLtest.online. Этот онлайн-сервис предоставляет множество интерактивных заданий, которые помогут вам закрепить теорию и отработать навыки написания SQL-запросов.

Продолжайте исследовать мир SQL! С каждым новым навыком вы будете открывать для себя все новые возможности для анализа и управления данными.