Обробка 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 і агрегати

Агрегатні функції ігнорують NULLs (крім COUNT(*)):

• COUNT(department_id) підраховує лише рядки зі значенням;
• AVG(salary) усереднює значення, ігноруючи NULLs — він не розглядає їх як 0;
• SUM(...) підсумовує лише значення, відмінні від NULL.

Це майже завжди те, що ви хочете. Але добре знати: якщо ви є шукаючи "загальну кількість працівників" використовуйте COUNT(*), а не COUNT(department_id).

Спробуйте

Повторна вправа