Значение NULL - это тип данных, представляющий неизвестное значение. Это не эквивалентно пустой строке или нулю. Предположим, у вас есть таблица сотрудников, содержащая такие столбцы, как EmployeeId
, Name
, ContactNumber
и альтернативный контактный номер. В этой таблице есть несколько обязательных столбцов значений, таких как EmployeeId
, Name
и ContactNumber
. Однако альтернативный контактный номер не требуется, поэтому его значение неизвестно. Следовательно, значение NULL в этой таблице представляет собой недостающую или неадекватную информацию. Вот другие значения NULL:
- Значение неизвестно
- Значение недоступно
- Атрибут не применим
В этом посте мы рассмотрим, как NULL используется при создании таблиц, запросах, строковых операциях и функциях. Скриншоты в этом посте взяты из Arctype SQL Client.
Разрешение NULL в CREATE TABLE
Для структуры таблицы нам нужно определить, разрешает ли соответствующий столбец NULL или нет. Например, взгляните на следующую таблицу клиентов. Такие столбцы, как CustomerID
, FirstName
, LastName
не допускают значений NULL, тогда как столбцы Suffix
, CompanyName
и SalesPerson
могут хранить значения NULL.
CREATE TABLE Customers(
CustomerID SERIAL PRIMARY KEY,
FirstName varchar(50) NOT NULL,
MiddleName varchar(50) NULL,
LastName varchar(50) NOT NULL,
Suffix varchar(10) NULL,
CompanyName varchar(128) NULL,
SalesPerson varchar(256) NULL,
EmailAddress varchar(50) NULL
)
Давайте вставим несколько записей в эту таблицу, используя следующий скрипт.
INSERT INTO Customers
(FirstName, MiddleName, LastName, Suffix, CompanyName, SalesPerson, EmailAddress)
VALUES
('John',NULL,'Peter',NULL,NULL,NULL,NULL),
('Raj','M','Mohan','Mr','ABC','KRS','[email protected]'),
('Krishna',NULL,'Kumar','MS','XYZ',NULL,'[email protected]')
Использование NULL в предложении WHERE
Теперь предположим, что вы хотите получить записи для тех клиентов, у которых нет адреса электронной почты. Следующий запрос работает нормально, но не выдаст нам строку:
Select * FROM Customers WHERE Emailaddress=NULL
В приведенном выше выражении оператора select определяется «Где адрес электронной почты равен НЕИЗВЕСТНОМУ значению». В стандарте SQL мы не можем сравнивать значение с NULL. Вместо этого для этой цели вы указываете значение IS NULL. Примечание. Между IS и NULL стоит пробел. Если вы удалите пробел, он станет функцией ISNULL ().
Целочисленные, десятичные и строковые операции с NULL
Точно так же предположим, что вы объявили переменную, но не инициализировали ее значение. Если вы попытаетесь выполнить арифметическую операцию, он также вернет NULL, потому что SQL не может определить правильное значение для переменной и считает НЕИЗВЕСТНОЕ значение.
SELECT 10 * NULL
SELECT 10.0 * NULL
NULL также играет важную роль в конкатенации строк. Предположим, вам требуется полное имя клиента в одном столбце, и вы объединяете их с помощью вертикальной черты (||).
SELECT Suffix, FirstName, MiddleName, LastName, Suffix,
(Suffix || ' ' || FirstName || ' ' || MiddleName || LastName ) AS CustomerFullName FROM Customers
Посмотрите на набор результатов - запрос возвращает NULL в объединенной строке, если какая-либо часть строки имеет NULL. Например, у человека в строке 1 нет отчества. Его объединенная строка также имеет значение NULL, потому что SQL не может проверить, что значение строки содержит NULL.
Существует множество функций SQL, позволяющих преодолеть эти проблемы с нулевым значением при конкатенации строк. Мы рассмотрим их позже в этой статье.
Значение NULL в агрегатах SQL
Предположим, вы используете агрегатные функции, такие как SUM, AVG или MIN, MAX для значений NULL. Как вы думаете, каким будет ожидаемый результат?
SELECT Sum(values) AS sum
,avg(values) as Avg
,Min(Values) as MinValue
,Max(Values) as MaxValue
FROM (VALUES (1), (2), (3),(4), (NULL)) AS a (values);
Посмотрите на приведенный выше рисунок: он рассчитал значения для всех агрегированных функций. SQL игнорирует значения NULL в агрегатных функциях за исключением COUNT () и GROUP BY (). Вы получите сообщение об ошибке, если попытаемся использовать агрегатную функцию для всех значений NULL.
SELECT
Sum(values) AS sum
,avg(values) as Avg
,Min(Values) as MinValue
,Max(Values) as MaxValue
FROM (VALUES (NULL), (NULL), (NULL),(NULL), (NULL)) AS a (values);
ORDER BY и GROUP BY с NULL
SQL считает значения NULL НЕИЗВЕСТНЫМИ значениями. Следовательно, если мы используем предложения ORDER By и GROUP by со столбцами со значениями NULL, он обрабатывает их одинаково и сортирует, группирует их. Например, в нашей таблице клиентов у нас есть NULL в столбце MilddleName
. Если мы сортируем данные с использованием этого столбца, в конце будут перечислены значения NULL, как показано ниже.
SELECT Suffix, FirstName, MiddleName, LastName, Suffix,
(Suffix || ' ' || FirstName || ' ' || MiddleName || LastName )
AS CustomerFullName
FROM Customers
Order BY MiddleName
Прежде чем использовать GROUP BY, давайте вставим еще одну запись в таблицу. Он имеет значения NULL в большинстве столбцов, как показано ниже.
INSERT INTO Customers (FirstName,MiddleName,LastName,Suffix,CompanyName,
SalesPerson,EmailAddress)
values('Sant',NULL,'Joseph',NULL,NULL,NULL,NULL);
Теперь используйте предложение GROUP BY для группировки записей на основе их суффикса.
SELECT count(*) as Customercount , suffix
FROM Customers
Group BY Suffix
Как показано выше, SQL одинаково обрабатывает эти значения NULL и группирует их. Вы получаете два количества клиентов для записей, для которых не указан суффикс в таблице клиентов.
Полезные функции для работы с NULL
Мы изучили, как SQL обрабатывает значения NULL в различных операциях. В этом разделе мы рассмотрим несколько ценных функций, чтобы избежать получения нежелательных значений из-за NULL.
Использование NULLIF в Postgres и MySQL
Функция NULLIF () сравнивает два входных значения.
● Если оба значения равны, она возвращает NULL.
● В случае несовпадения она возвращает первое значение в качестве выходных данных.
Например, посмотрите на вывод следующих функций NULLIF ().
SELECT NULLIF (1, 1);
SELECT NULLIF (100,0);
SELECT NULLIF ('A', 'Z');
КОЛЕСЦ функция
Функция COALESCE () принимает несколько входных значений и возвращает первое значение, отличное от NULL. Мы можем указать различные типы данных в одной функции COALESCE () и вернуть тип данных с высоким приоритетом.
SELECT COALESCE (NULL, 2, 5) AS NULLRESPONSE;
SELECT coalesce(null, null, 8, 2, 3, null, 4);
Резюме
Тип значения NULL требуется в реляционной базе данных для представления неизвестного или отсутствующего значения. Вам необходимо использовать соответствующую функцию SQL, чтобы избежать нежелательного вывода для таких операций, как конкатенация данных, сравнение, ORDER BY или GROUP BY. Вы не должны пытаться предотвратить значения NULL - вместо этого напишите свой запрос таким образом, чтобы преодолеть его ограничения. Так вы научитесь любить NULL.