Обработка значений NULL: IS NULL и COALESCE

В наборе данных employees Андреа Коста особенный: у него есть department_id = NULL. Вы уже столкнулись с NULL (с LEFT JOIN), но пришло время отнестись к этому серьезно: NULLs в SQL ведут себя отлично от любых других значений, и игнорирование их является одним из основные причины ошибок в производственном коде.

NULL не ноль и не пустая строка

NULL означает неизвестное/отсутствующее значение. Это не 0, не '', не false. Это "Я не знаю". Отсюда следует удивительное свойство: NULL не равен ничему, даже самому себе.

-- All these expressions return NULL (not true!):
SELECT NULL = NULL;       -- NULL
SELECT NULL = 0;          -- NULL
SELECT NULL <> 'qualcosa'; -- NULL

Конкретное следствие: WHERE column = NULL никогда не работает. Вы должны использовать выделенные операторы IS NULL / IS NOT NULL:

-- Employees without a department:
SELECT first_name, last_name
FROM   employees
WHERE  department_id IS NULL;

-- Employees WITH a department:
SELECT first_name, last_name
FROM   employees
WHERE  department_id IS NOT NULL;

COALESCE — NULL-резервный вариант

Часто вы хотите отобразить что-то вместо NULL, когда данные отсутствует: строка '(none)', ноль, дата по умолчанию. Функция, которая именно это делает COALESCE(a, b, c, ...): он возвращает первый аргумент, отличный от NULL в списке.

-- Show the department_id, or 0 if missing:
SELECT first_name, last_name, COALESCE(department_id, 0) AS dept
FROM   employees;

В сочетании с LEFT JOIN это становится невероятно мощным средством для отчетов:

SELECT e.first_name,
       e.last_name,
       COALESCE(d.name, '(nessun dipartimento)') AS dipartimento
FROM   employees   AS e
LEFT JOIN departments AS d ON e.department_id = d.id;

Результат: все сотрудники вернулись с названием отдела или явным этикетка для тех, у кого ее нет.

NULL и агрегаты

Агрегатные функции игнорируют NULL (кроме COUNT(*)):

• COUNT(department_id) учитывает только строки со значением;
• AVG(salary) усредняет значения, игнорируя NULL — он не рассматривает их как 0;
• SUM(...) суммирует только значения, отличные от NULL.

Это почти всегда то, что вы хотите. Но полезно знать: если вы для поиска "общего количества сотрудников" используйте COUNT(*), а не COUNT(department_id).

Попробуйте это

Обзор упражнения