Работа со значениями NULL
1. Что такое значение NULL. Особенности работы со значениями NULL.
Значение NULL - это неизвестное значение, неопределенное значение, отсутствующее значение. NULL используется в тех случаях, когда у нас отсутствует какое то значение и мы просто не знаем, что записать в колонку таблицы.
Сравнение с NULL
SELECT id, name, parent_id FROM music_instrument WHERE parent_id IS NULL;
То есть при сравнении с NULL используется не =, а IS
Если нужно вывести значения, которые не равны NULL, используется NOT
SELECT id, name, parent_id FROM music_instrument WHERE parent_id IS NOT NULL;
Функция COALESCE
Функция COALESCE - заменяет значение NULL в первом аргументе на значение, указанное во втором аргументе.
SELECT id, name, parent_id, COALESCE(parent_id, -1) as parent_id2 FROM music_instrument;
a + b: ВНИМАНИЕ! или Сложение двух колонок с числами
SELECT a,
b,
a + b as c
FROM mytable_int
;
-- Ввод: a = 1, b = 5, тогда с = 6
-- Ввод: a = 2, b = [null], тогда с = [null]То есть при математической операции любого числа с NULL результат будет всегда NULL.
В случае, чтобы число не потерялось при выполнении математических операций с NULL, можно написать такой запрос, чтобы NULL заменить на 0.
SELECT
a,
b,
--
a + b as c,
--
coalesce(a, 0) as a2,
coalesce(b, 0) as b2,
--
coalesce(a, 0) + coalesce(b, 0) as c2
FROM mytable_int
;
str_a || str_b: ВНИМАНИЕ! или Конкатенация строк
SELECT
str_a,
str_b,
str_a || str_b as str_c
FROM mytable_str
;
Любая конкатенация с NULL будет давать NULL
В случае, чтобы строка не потерялось при выполнении склеивания с NULL, можно написать такой запрос, чтобы NULL заменить на пустую строку ('').
SELECT
str_a,
str_b,
--
str_a || str_b as str_c,
--
coalesce(str_a, '') as str_a2,
coalesce(str_b, '') as str_b2,
--
coalesce(str_a, '') || coalesce(str_b, '') as str_c2
FROM mytable_str
;
2. NULL и Сравнение "не равно"
Разберем, как значение NULL работает с неравенством
SELECT * FROM mytable_int
А теперь выведем строку, где a = 1
SELECT * FROM mytable_int WHERE a = 1
А теперь выберем все строки, где a не равно 1
SELECT * FROM mytable_int WHERE a <> 1
Запрос вывел только 3 строки, хотя должен был вывести 4. Но не вывел строку, в которой a = NULL
А теперь отдельно выведем строку, где a = NULL
SELECT * FROM mytable_int WHERE a IS NULL
Чтобы разобраться, что происходит, давайте запишем такой код
SELECT a, b, a = b as ab FROM mytable_int WHERE a = 1
То есть a = 1, b = 5, следовательно false потому что 1 не равен 5.
SELECT a, b, a = 1 as ab FROM mytable_int WHERE a = 1
SELECT a, b, a = 1, a <> 1 FROM mytable_int WHERE a = 1
SELECT a, b, a = 1, a <> 1, NOT (a = 1) FROM mytable_int WHERE a = 1
А теперь запустим выражение, где a IS NULL
В сравнении с таблицей выше, нет булевых значений, а только везде NULL
Для наглядности выведем всю таблицу (уберем условие WHERE)
SELECT a, b, a = 1, a <> 1, NOT (a = 1) FROM mytable_intИз этой же таблицы выберем строчки, где a <> b
SELECT * FROM mytable_int WHERE a <> b
Как вывести значение с NULL в этой ситуации?
SELECT *
FROM mytable_int
WHERE a <> b
OR (a IS NULL AND b IS NOT NULL)
OR (a IS NOT NULL AND b IS NULL)
НАИБОЛЕЕ ЧАСТО ТАКАЯ ОШИБКА ИСПОЛЬЗУЕТСЯ ПРИ ВЫЗОВЕ НЕРАВЕНСТВ!
То есть первоначальный код (вызовы в самом начале) должны быть записаны так:
SELECT * FROM mytable_int WHERE a = 1
SELECT * FROM mytable_int WHERE a <> 1 OR a IS NULL
Первым запросом вызываем все строки, где a = 1, а вторым запросом все строки где a не равно 1
IN и NOT IN при наличии значений NULL
IN
Представим, что в теоретической таблицу list1 есть 1 значения id - 303, 93, [null].
SELECT band_id, name FROM band WHERE band_id IN (SELECT id FROM list1)
SELECT band_id, name FROM band WHERE band_id IN (303, 93, NULL)
SELECT band_id, name FROM band WHERE band_id IN (303, 93, NULL)
То есть то, что NULL есть в запросе на результат не влияет.
А если добавить в таблицу band запись, где band_id = NULL, тогда запрос:
SELECT band_id, name FROM band WHERE band_id IN (SELECT id FROM list1)
То есть значение, где band_id равен NULL не вернулось.
NOT IN
SELECT band_id, name
FROM band
WHERE band_id NOT IN
(SELECT id FROM list1
);
Вернул пустую таблицу. То есть в этом случае обнуляет все запросы. Более подробно видно на запросе ниже (записать наш запрос другими словами):
SELECT band_id, name
FROM band_id
WHERE
band_id <> 303
AND band_id <> 93
AND band_id <> NULLКак обезопасить себя от такого поведения NOT IN?
SELECT band_id, name
FROM band
WHERE band_id NOT IN
(SELECT id
FROM list1
WHERE id IS NOT NULL)