Microsoft SQL Server
Значения NULL

Вступление

замечания

Сравнение NULL

NULL - это особый случай, когда дело касается сравнений.

Предположим следующие данные.

id someVal
 ----
 0 NULL
 1 1
 2 2

С запросом:

 SELECT id
 FROM table
 WHERE someVal = 1

вернет id 1

 SELECT id
 FROM table
 WHERE someVal <> 1

вернет id 2

 SELECT id
 FROM table
 WHERE someVal IS NULL

вернет id 0

 SELECT id
 FROM table
 WHERE someVal IS NOT NULL

вернет оба идентификатора 1 и 2 .

Если вы хотите, чтобы NULL «подсчитывались» как значения в сравнении a = , <> , его сначала нужно преобразовать в счетный тип данных:

 SELECT id
 FROM table
 WHERE ISNULL(someVal, -1) <> 1

ИЛИ ЖЕ

 SELECT id
 FROM table
 WHERE someVal IS NULL OR someVal <> 1

возвращает 0 и 2

Или вы можете изменить настройку ANSI Null .

ANSI NULLS

Из MSDN

В будущей версии SQL Server ANSI_NULLS всегда будет включен, и любые приложения, которые явно устанавливают значение OFF, генерируют ошибку. Избегайте использования этой функции в новых разработках и планируйте изменять приложения, которые в настоящее время используют эту функцию.

ANSI NULLS , устанавливаемый в off, позволяет использовать a = / <> сравнение нулевых значений.

Учитывая следующие данные:

id someVal
 ----
 0 NULL
 1 1
 2 2

И с ANSI NULLS on этот запрос:

 SELECT id
 FROM table
 WHERE someVal = NULL

не даст никаких результатов. Однако тот же запрос, когда ANSI NULLS выключен:

 set ansi_nulls off

 SELECT id
 FROM table
 WHERE someVal = NULL

Вернул бы id 0 .

НУЛЕВОЙ()

Функция IsNull() принимает два параметра и возвращает второй параметр, если первый имеет значение null .

Параметры:

1. проверьте выражение. Любое выражение любого типа данных.
2. восстановительная стоимость. Это значение, которое будет возвращено, если выражение проверки равно null. Значение замены должно быть типа данных, которое может быть неявно преобразовано в тип данных выражения проверки.

Функция IsNull() возвращает тот же тип данных, что и выражение проверки.

DECLARE @MyInt int -- All variables are null until they are set with values.

SELECT ISNULL(@MyInt, 3) -- Returns 3.

См. Также COALESCE , выше

Is null / Is not null

Поскольку значение null не является значением, вы не можете использовать операторы сравнения с нулями.
Чтобы проверить, имеет ли столбец или переменную значение null, вам нужно использовать значение is null :

DECLARE @Date date = '2016-08-03'

Следующий оператор выберет значение 6 , так как все сравнения с нулевыми значениями оцениваются как ложные или неизвестные:

SELECT CASE WHEN @Date = NULL THEN 1
            WHEN @Date <> NULL THEN 2
            WHEN @Date > NULL THEN 3
            WHEN @Date < NULL THEN 4
            WHEN @Date IS NULL THEN 5
            WHEN @Date IS NOT NULL THEN 6

Задав для содержимого переменной @Date значение null и повторите попытку, следующий оператор вернет 5 :

SET @Date = NULL -- Note that the '=' here is an assignment operator!

SELECT CASE WHEN @Date = NULL THEN 1
            WHEN @Date <> NULL THEN 2
            WHEN @Date > NULL THEN 3
            WHEN @Date < NULL THEN 4
            WHEN @Date IS NULL THEN 5
            WHEN @Date IS NOT NULL THEN 6

COALESCE ()

COALESCE () Вычисляет аргументы в порядке и возвращает текущее значение первого выражения, которое изначально не оценивается в NULL .

DECLARE @MyInt int -- variable is null until it is set with value.
DECLARE @MyInt2 int -- variable is null until it is set with value.
DECLARE @MyInt3 int -- variable is null until it is set with value.

SET @MyInt3  = 3

SELECT COALESCE (@MyInt, @MyInt2 ,@MyInt3 ,5) -- Returns 3 : value of @MyInt3.

Хотя ISNULL () работает аналогично COALESCE (), функция ISNULL () принимает только два параметра - один для проверки и один для использования, если первый параметр равен NULL. См. Также ISNULL , ниже

NULL с NOT IN SubQuery

При обработке не в подзапросе с нулевым значением в подзапросе нам нужно устранить NULLS, чтобы получить ожидаемые результаты

create table #outertable (i int)
create table #innertable (i int)

insert into #outertable (i) values (1), (2),(3),(4), (5)
insert into #innertable (i) values (2), (3), (null)

select * from #outertable where i in (select i from #innertable)
--2
--3
--So far so good

select * from #outertable where i not in (select i from #innertable)
--Expectation here is to get 1,4,5 but it is not. It will get empty results because of the NULL it executes as {select * from #outertable where i not in (null)}

--To fix this 
select * from #outertable where i not in (select i from #innertable where i is not null)
--you will get expected results
--1
--4
--5

При обработке не в подзапросе с нулем будьте осторожны с ожидаемым результатом