Использование значения NULL в условиях поиска
позволяет проверить отсутствие (наличие) значения в полях таблицы. Использование в этих случаях обычных предикатов сравнения может привести к неверным результатам, так как сравнение со значением NULL дает результат UNKNOWN (неизвестно).
Так, если требуется найти записи в таблице PC, для которых в столбце price отсутствует значение (например, при поиске ошибок ввода), можно воспользоваться следующим оператором:
Консоль
Выполнить
Характерной ошибкой является написание предиката в виде:
Этому предикату не соответствует ни одной строки, поэтому результирующий набор записей будет пуст, даже если имеются изделия с неизвестной ценой. Это происходит потому, что сравнение с NULL -значением согласно предикату сравнения оценивается как UNKNOWN . А строка попадает в результирующий набор только в том случае, если предикат в предложении WHERE есть TRUE . Это же справедливо и для предиката в предложении HAVING .
Аналогичной, но не такой очевидной ошибкой является сравнение с NULL в предложении CASE (см. пункт 5.10). Чтобы продемонстрировать эту ошибку, рассмотрим такую задачу: «Определить год спуска на воду кораблей из таблицы Outcomes. Если последний неизвестен, указать 1900».
Поскольку год спуска на воду (launched) находится в таблице Ships, нужно выполнить левое соединение (см. пункт 5.6):
Консоль
Выполнить
Для кораблей, отсутствующих в Ships, столбец launched будет содержать NULL -значение. Теперь попробуем заменить это значение значением 1900 с помощью оператора CASE (см. пункт 5.10):
Консоль
Выполнить
Однако ничего не изменилось. Почему? Потому что использованный оператор CASE эквивалентен следующему:
А здесь мы получаем сравнение с NULL -значением, и в результате — UNKNOWN , что приводит к использованию ветви ELSE, и все остается, как и было. Правильным будет следующее написание:
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:
- Как назначить значение NULL в SQL?
Значение NULL можно явно указать при создании таблицы или добавления записей в таблицу. Например, при создании таблицы можно указать, что один из столбцов не обязательно должен иметь значение, используя ключевое слово NULL. - Как проверить NULL в SQL?
Для того чтобы проверить значение NULL в SQL, используется оператор IS NULL. Этот оператор возвращает true, если значение столбца равно NULL. - NULL и требования целостности данных
NULL может нарушить требования целостности данных, которые гарантируют, что данные в таблице являются валидными и согласованными. Например, требование необходимости заполнения поля значением может быть нарушено, если значение NULL допустимо. - Ограничение NOT NULL
Ограничение NOT NULL позволяет определить, что значение в столбце не может быть NULL. Это означает, что при добавлении записи в таблицу обязательно должно быть заполнено значение для данного столбца. - ISNULL
Функция ISNULL возвращает первый аргумент, если он не равен NULL, и второй аргумент, если первый аргумент равен NULL. ISNULL наиболее часто используется для замены значений NULL на конкретные значения. - COALESCE
Функция COALESCE возвращает первый аргумент, который не равняется NULL. COALESCE может быть полезна, когда вам нужно выбрать первое значение из двух или нескольких, которые могут быть пустыми. - NULLIF
Функция NULLIF возвращает NULL, если два аргумента равны. Если аргументы не равны, она возвращает первый аргумент. Эта функция может быть полезна для условного выполнения некоторых операций в зависимости от того, равны ли значения.
Почему знание NULL важно для SQL-разработчиков?
Понимание того, что такое NULL и как он работает, важно для SQL-разработчиков, так как они должны убедиться, что данные в таблице корректны и не содержат NULL, если это не предусмотрено требованиями для соответствующего столбца таблицы. Также знание правильной работы с NULL в SQL позволяет избежать неожиданного поведения запросов и операторов, которые могут привести к ошибкам или неверным результатам. Кроме того, понимание того, как обрабатывать значения NULL, может улучшить эффективность запросов, так как правильное использование функций для работы с NULL может сократить количество кода и убрать дублирование.
NULL в базе данных может привести к ошибкам, например:
- Сравнение значений. Если в таблице присутствуют значения NULL, то при выполнении операции сравнения, например, WHERE column_name = NULL, результатом будет False. Вместо этого нужно использовать оператор IS NULL.
- Вычисления. Если при выполнении арифметических операций включены значения NULL, то результат такой операции тоже будет NULL. Например, 5 + NULL = NULL.
- Сортировка. При сортировке значений в столбце, которые содержат NULL, может произойти непредсказуемый результат в зависимости от реализации сортировки в базе.
- Внешние ключи. Если в таблице соединения используются внешние ключи, то значение NULL может привести к нарушению связной целостности.
- Агрегирующие функции. При использовании агрегирующих функций в запросах, значения NULL могут не быть учтены в результате.
- Вывод на экран. Если значение NULL выводится на экран пользователя, это может вызвать возможное недопонимание и ухудшение пользовательского опыта.
Все эти проблемы могут привести к ошибкам при обработке данных и привести к неправильным результатам. Необходимо быть осторожным при работе с NULL значениями в базе данных и учитывать их взаимодействие при проектировании и разработке баз данных.
Пример неудачного использования NULL
Допустим, у нас есть таблица, в которой хранится информация о заказах в интернет-магазине. Среди полей есть поля, отражающие дату создания заказа (orderdate) и дату его доставки (deliverydate).
Однажды в этой таблице обнаружилась ошибка: у нескольких заказов deliverydate было не заполнено, т.е. им было присвоено значение NULL. Разработчики не заметили этого и продолжили работу с данными.
Однако при анализе статистики продаж на одном из графиков заказы отображались в зависимости от даты доставки. Из-за того, что несколько заказов не имели значения в поле deliverydate, они не отображались на графике вовсе, что привело к искажению реальных данных и ошибочным выводам о продажах на определенные даты.
Эта ошибка привела к тому, что команда интернет-магазина долго работала с неточными данными, и необходимо было потратить много времени на исправление ошибки и калибровку аналитических инструментов. Все эти проблемы могли быть исправлены, если бы разработчики были внимательными и не допустили присвоения значения NULL в поле, которое требует обязательного заполнения.
В итоге, знание NULL очень важно для SQL-разработчиков, так как неправильное использование NULL может привести к ошибкам в запросах и значительно затруднить дальнейшую обработку данных. Однако, правильное использование NULL может упростить запросы и дать возможность корректно хранить и обрабатывать данные. Поэтому, при работе с базами данных, SQL-разработчикам необходимо быть внимательными и осознанными в использовании NULL.
Бонус
Три вопроса с собеседований, где вас проверяют на знание NULL в SQL:
- Как проверить, есть ли NULL значение в определенном столбце таблицы в SQL? Ответ: Необходимо использовать оператор «IS NULL» или «IS NOT NULL». Например, чтобы проверить, есть ли NULL значение в столбце «name» таблицы «users», нужно выполнить следующий запрос: SELECT FROM users WHERE name IS NULL;
- Как можно заменить NULL значения на определенное значение в SQL? Ответ: Для замены NULL значений можно использовать оператор «COALESCE». Например, чтобы заменить NULL значения в столбце «price» таблицы «products» на значение 0, нужно выполнить следующий запрос: SELECT COALESCE(price, 0) FROM products;
- Как можно проверить, что два столбца имеют одинаковые значения, включая NULL, в SQL? Ответ: Для этого нужно использовать оператор «IS NOT DISTINCT FROM». Он сравнивает значения двух столбцов, включая NULL значения. Например, чтобы проверить, что значения столбцов «name» и «address» в таблице «users» совпадают, нужно выполнить следующий запрос: SELECT FROM users WHERE name IS NOT DISTINCT FROM address;
Nullable(TypeName)
Позволяет работать как со значением типа TypeName так и с отсутствием этого значения (NULL) в одной и той же переменной, в том числе хранить NULL в таблицах вместе со значения типа TypeName . Например, в столбце типа Nullable(Int8) можно хранить значения типа Int8 , а в тех строках, где значения нет, будет храниться NULL .
В качестве TypeName нельзя использовать составные типы данных Array и Tuple. Составные типы данных могут содержать значения типа Nullable , например Array(Nullable(Int8)) .
Поле типа Nullable нельзя включать в индексы.
NULL — значение по умолчанию для типа Nullable , если в конфигурации сервера ClickHouse не указано иное.
Особенности хранения
Для хранения значения типа Nullable ClickHouse использует:
- Отдельный файл с масками NULL (далее маска).
- Непосредственно файл со значениями.
Маска определяет, что лежит в ячейке данных: NULL или значение.
В случае, когда маска указывает, что в ячейке хранится NULL , в файле значений хранится значение по умолчанию для типа данных. Т.е. если, например, поле имеет тип Nullable(Int8) , то ячейка будет хранить значение по умолчанию для Int8 . Эта особенность увеличивает размер хранилища.
Примечание
Почти всегда использование Nullable снижает производительность, учитывайте это при проектировании своих баз.
Поиск NULL
Найти в столбце значения NULL можно с помощью подстолбца null , при этом весь столбец считывать не требуется. Подстолбец содержит 1 , если соответствующее значение равно NULL , и 0 если не равно.
Пример
CREATE TABLE nullable (`n` Nullable(UInt32)) ENGINE = MergeTree ORDER BY tuple(); INSERT INTO nullable VALUES (1) (NULL) (2) (NULL); SELECT n.null FROM nullable; Неполные данные и null
Что может означать тот факт, что у студента null в столбце GroupId?
- Значение неизвестно (нет информации, из какой группы студент)
- Значение неверно (студент учится в какой-то группе, но эта группа не представлена в БД)
- Значение еще/уже не существует (студент был зачислен, но еще не распределен в группу или уже отчислен)
- Значение не имеет смысла (студент из другого университета, который пришел с какими-то целями в ИТМО)
- Значение недоступно (недостаточно прав узнать группу)
На основе этих предположений можно сделать вывод, что значение null сильно зависит от контекста (какую предметную область мы моделируем итд.).
Вполне возможно, что возникнет необходимость различать разные виды того, что значение в том или ином смысле отсутствует.
Можно ли обойтись без null?
Как представить кортеж с неопределенными частями в нашем случае?
- Разбить на 2 группы и сделать необязательную связь 1:1. В таком случае, в дополнительной таблице будет запись (StudentId, GroupId) тогда и только тогда, когда у студента определена группа
Где еще появляется null
- Результаты внешних соединений
- Результаты множественных операций
Оказывается, что в некоторых случаях без null не обойтись и надо уметь с ним работать.
Тернарная логика с использованием null
С точки зрения SQL, результат логического выражения может быть true, false или unknown.
С другой стороны есть тип boolean, и у него есть 3 значения: true, false и null
То есть формально unknown — это результат вычисления, а null — это конкретное значение, которое может быть записано в БД. На практике unknown представляется значением null, и это различие не будет иметь большого значения.
Конъюнкция
| [math]\bf[/math] | [math]\bf[/math] | [math]\bf[/math] | [math]\bf[/math] |
|---|---|---|---|
| [math]\bf[/math] | [math]true[/math] | [math]unknown[/math] | [math]false[/math] |
| [math]\bf[/math] | [math]unknown[/math] | [math]unknown[/math] | [math]false[/math] |
| [math]\bf[/math] | [math]false[/math] | [math]false[/math] | [math]false[/math] |
Дизъюнкция
| [math]\bf[/math] | [math]\bf[/math] | [math]\bf[/math] | [math]\bf[/math] |
|---|---|---|---|
| [math]\bf[/math] | [math]true[/math] | [math]true[/math] | [math]true[/math] |
| [math]\bf[/math] | [math]true[/math] | [math]unknown[/math] | [math]unknown[/math] |
| [math]\bf[/math] | [math]true[/math] | [math]unknown[/math] | [math]false[/math] |
Отрицание
| [math][/math] | [math]\bf[/math] | [math]\bf[/math] | [math]\bf[/math] |
|---|---|---|---|
| [math]\bf[/math] | [math]false[/math] | [math]unknown[/math] | [math]true[/math] |
Сравнение
Равенство
| [math]\bf[/math] | [math]\bf[/math] | [math]\bf[/math] | [math]\bf[/math] |
|---|---|---|---|
| [math]\bf[/math] | [math]true[/math] | [math]unknown[/math] | [math]false[/math] |
| [math]\bf[/math] | [math]unknown[/math] | [math]unknown[/math] | [math]unknown[/math] |
| [math]\bf[/math] | [math]false[/math] | [math]unknown[/math] | [math]true[/math] |
is
| [math][/math] | [math]\bf[/math] | [math]\bf[/math] | [math]\bf[/math] |
|---|---|---|---|
| [math]\bf[/math] | [math]true[/math] | [math]false[/math] | [math]false[/math] |
| [math]\bf[/math] | [math]false[/math] | [math]true[/math] | [math]false[/math] |
| [math]\bf[/math] | [math]false[/math] | [math]false[/math] | [math]true[/math] |
| [math]\bf[/math] | [math]false[/math] | [math]true[/math] | [math]true[/math] |
| [math]\bf[/math] | [math]true[/math] | [math]false[/math] | [math]true[/math] |
| [math]\bf[/math] | [math]true[/math] | [math]true[/math] | [math]false[/math] |
Проблемы при работе с null
При работе с null в процессе разработки БД, во избежание непредвиденных ошибок, необъодимо заранее ознакомиться с тем, какие проблемы могут возникнуть.
Вывод логических выражений
В новой тернарной логике работают не все правила преобразований, присущие двоичной. Например, нельзя полагать, что [math](A\ \vee\ \neg\ A)[/math] всегда истинно, потому что теперь может получиться unknown.
Поэтому при каждом преобразовании троичного логического выражения, лучше сверяться с таблицами истинности.
Скалярные операции, порождающие null
Следующие операции с null порождают null, и иногда это может сбивать с толку начинающих разработчиков.
- [math]=[/math] , [math]\lt \gt [/math] , [math]\lt [/math] , [math]\lt =[/math] , [math]\gt [/math] , [math]\gt =[/math]
- [math]+[/math] , [math]−[/math] , [math]*[/math] , [math]/[/math]
- [math]\|[/math]
- [math]in[/math]
Рассмотрим несколько примеров.
select (1 + null) from Students;
Не смотря на то, что этот запрос не несет большого смысла, на его примере можно убедиться, что в арифметических операциях null «заразен».
select StudentId from Students where GroupId = null;
Это частая ошибка, сравнение с null дает unknown, а значит запрос вернет пустую таблицу.
Говоря об операции сравнения, стоит отметить, что она не транзитивна и не рефлексивна.
- [math]x\ =\ x[/math] — true или null
- [math]x\ \lt \gt \ x[/math] — true или null
- [math]x\ or\ x[/math] — true или null
- [math]x\ or\ not\ x[/math] — true или null
- [math]x\ and\ not\ x[/math] — false или null
Дубликаты и null
Так как null ≠ null, сравнения кортежей, содержащих null не обладают интуитивными свойствами, например:
- [math]R \cup R[/math] — не всегда [math]R[/math]
- [math]R \cap R[/math] — не всегда [math]R[/math]
- [math]R \bowtie R[/math] — не всегда [math]R[/math]
Неинтуитивность null
Рассмотрим запрос, для нахождения студентов не из группы ‘M34391’.
select * from Students where GroupId <> 'M34391'
Корректность запроса зависит от смысла null. Неясно, надо ли возвращать в этом запросе студента, о котором нет информации, в какой группе он учится.
Следующий запрос, хоть и выглядит странно, предполагает просто поиск всевозможных студентов
select * from Students where GroupId <> 'M34391' union select * from Students where GroupId = 'M34391'
Но из-за наличия null, этот запрос не отработает так, как предполагалось. Если GroupId студента null, то сравнение не вернет true, а значит в результате это учтено не будет.
Подробнее у работе функции where будет рассказано в следующем разделе.
Работа с null в SQL
Несмотря на множество проблем, описанных выше, в SQL существуют механизмы, позволяющие корректно обработать null.
Проверки значений
Для сравнения с null используется is null (или is not null ). Получить всех студентов с null в поле GroupId можно следующим образом:
select StudentId from Students where GroupId is null;
В общем виде синтаксис проверки значений выглядит следующим образом: значение is [ not ] < null |true|false|unknown >, например:
- x is not true
- x or x is not null
Так же в SQL существует функция coalesce(v1, v2, . ), которая принимает произвольное число аргументов и возвращает первый не не null. Если все аргументы null, то возвращает null.
Ключи и null
Можно использовать null:
- Альтернативные ключи
- Внешние ключи
- Простые
- Составные, отсутствующие целиком
Первичные ключи не могут содержать null.
Предикаты
DML
where и having считают истинным предикат только если он вернул true Зная этот факт можно, например убедиться, что false and unknown дает false. Следующий запрос вернет 1:
select 1 where not (0 = 1 and 0 = null)
DDL
C точки зрения check constraint-ов не подходит только false. Unknown превращается в true
Различимость
Два null не равны и не различимы. Это важно для distinct и group by
Например, кортежи (1, null) и (1, null) склеятся в случае distinct и не породят разные группы в случае group by , т.к. не различимыТипы столбцов
В SQL столбцы могут быть nullable (по умолчанию) и не nullable
birthday date birthday date not null
Перед созданием nullable столбца, рекомендуется дополнительно обдумать, какой конкретно смысл вкладывается в null в данном случае, не скажется ли это негативно на остальных запросах, в случае, если начать его использовать. Если есть возможность, во избежание дополнительных проблем, описанных выше, лучше объявлять столбцы not null.
Прочее
- exists
- возвращает true или ‘false’
- если внутри получились только строки, состоящие из null, то вернет так же false
- пропускают null, т.е. не учитывают его при подсчете
- при отсутствии аргументов, отличных от null, возвращают null
- исключением является count (*), что просто считает количество строк
- Помимо указаний порядка сортировки (asc или desc), можно указывать, куда ставить null-ы — в начало или в конец. Например order by year nulls first. По умолчанию null-ы складываются либо в начало, либо в конец, это нужно уточнять в документации к конкретной СУБД.