Использование значения null в условиях поиска

Предикат:

IS [NOT] NULL

позволяет проверить отсутствие (наличие) значения в полях таблицы. Использование в этих случаях обычных предикатов сравнения может привести к неверным результатам, так как сравнение со значением NULL дает результат UNKNOWN (неизвестно).

Так, если требуется найти записи в таблице PC, для которых в столбце price отсутствует значение (например, при поиске ошибок ввода), можно воспользоваться следующим оператором:

1. SELECT *

2. FROM PC

3. WHERE price IS NULL

Характерной ошибкой является написание предиката в виде: WHERE price = NULL;

Этому предикату не соответствует ни одной строки, поэтому результирующий набор записей будет пуст, даже если имеются изделия с неизвестной ценой. Это происходит потому, что сравнение с NULL-значением согласно предикату сравнения оценивается как UNKNOWN. А строка попадает в результирующий набор только в том случае, если предикат в предложении WHERE есть TRUE. Это же справедливо и для предиката в предложении HAVING.

Аналогичной, но не такой очевидной ошибкой является сравнение с NULL в предложении CASE 

Чтобы продемонстрировать эту ошибку, рассмотрим такую задачу: «Определить год спуска на воду кораблей из таблицы Outcomes. Если последний неизвестен, указать 1900».

Поскольку год спуска на воду (launched) находится в таблице Ships, нужно выполнить левое соединение

1. SELECT ship, launched

2. FROM Outcomes o LEFT JOIN

3. Ships s ON o.ship = s.name;

Для кораблей, отсутствующих в Ships, столбец launched будет содержать NULL-значение. Теперь попробуем заменить это значение значением 1900 с помощью оператора CASE

1. SELECT ship, CASE launched

2. WHEN NULL

3. THEN 1900

4. ELSE launched

5. END 'year'

6. FROM Outcomes o LEFT JOIN

7. Ships s ON o.ship=s.name;

Однако ничего не изменилось. Почему? Потому что использованный оператор CASE эквивалентен следующему:

1. CASE

2. WHEN launched = NULL

3. THEN 1900

4. ELSE launched

5. END 'year';

А здесь мы получаем сравнение с NULL-значением, и в результате — UNKNOWN, что приводит к использованию ветви ELSE, и все остается, как и было. Правильным будет следующее написание:

1. CASE

2. WHEN launched IS NULL THEN 1900

3. ELSE launched

4. END 'year';

то есть проверка именно на присутствие NULL-значения.

Функция datepart

Синтаксис:

DATEPART(datepart , date)

Эта функция возвращает целое число, представляющее собой указанную аргументом datepart часть заданной вторым аргументом даты (date).

Пример: Определить дату и время вылета рейса 1123.

В таблице совершенных рейсов Pass_in_trip содержится только дата рейса, но не время, так как в соответствии с предметной областью каждый рейс может выполняться только один раз в день. Для решения этой задачи нужно к дате, хранящейся в таблице Pass_in_trip, добавить время из таблицы Trip

1. SELECT DISTINCT pt.trip_no,

2. DATEADD(mi, DATEPART(hh,time_out)*60 +

3. DATEPART(mi,time_out), date) [time]

4. FROM Pass_in_trip pt JOIN

5. Trip t ON pt.trip_no = t.trip_no

6. WHERE t.trip_no = 1123;

DISTINCT необходим здесь, чтобы исключить возможные дубликаты, поскольку номер и дата рейса дублируются в этой таблице для каждого пассажира данного рейса.

В ряде случаев функцию DATEPART можно заменить более простыми функциями. Вот они:

DAY(date) — целочисленное представление дня указанной даты. Эта функция эквивалентна функции DATEPART(dd, date).

MONTH (date) — целочисленное представление месяца указанной даты. Эта функция эквивалентна функции DATEPART(mm, date).

YEAR(date) — целочисленное представление года указанной даты. Эта функция эквивалентна функции DATEPART(yy, date).