NULL в Oracle SQL — как работать с пустыми значениями

NULL в Oracle SQL. Введение

NULL в Oracle SQL — это специальное значение, обозначающее «неизвестное» или «отсутствующее». Оно не эквивалентно нулю, пустой строке или FALSE. В этой статье мы разберём, как правильно использовать NULL, избегать ошибок сравнения и обрабатывать его в условиях, выражениях и функциях.

🖋️ Синтаксис и ключевые правила работы с NULL

Проверка значения:

WHERE column IS NULL
WHERE column IS NOT NULL

Сравнение недопустимо:

-- Неправильно:
WHERE column = NULL
-- Правильно:
WHERE column IS NULL

Обработка NULL:

NVL(column, значение_по_умолчанию)
COALESCE(expr1, expr2, ...)
NULLIF(expr1, expr2)

🔄 Где часто встречается NULL

Отсутствие значений в колонках (salary, manager_id и т. д.)
Условная логика и выбор альтернативных значений
Агрегации, фильтрации и сравнение значений
Работа с текстом, числом, датой и логикой в PL/SQL

📊 50 примеров использования NULL в Oracle SQL

Поиск NULL:

SELECT * FROM employees WHERE commission_pct IS NULL;

Проверка IS NOT NULL:

SELECT * FROM employees WHERE manager_id IS NOT NULL;

Использование NVL:

SELECT first_name, NVL(commission_pct, 0) FROM employees;

Использование COALESCE:

SELECT first_name, COALESCE(commission_pct, 0.05) FROM employees;

NULLIF для исключения равных:

SELECT NULLIF(salary, bonus) FROM employees;

NULL в арифметике:

SELECT salary + NULL FROM employees;

NVL с текстом:

SELECT NVL(last_name, 'Неизвестно') FROM employees;

NVL2 с логикой:

SELECT NVL2(commission_pct, 'Есть бонус', 'Нет бонуса') FROM employees;

COALESCE с множественными значениями:

SELECT COALESCE(commission_pct, bonus, 0) FROM employees;

NULL в условии CASE:

SELECT first_name,
  CASE WHEN commission_pct IS NULL THEN 'Без бонуса' ELSE 'С бонусом' END AS бонус
FROM employees;

NULL в подзапросе:

SELECT * FROM employees WHERE department_id IS NULL OR department_id IN (
  SELECT department_id FROM departments WHERE location_id IS NULL
);

NULL и ORDER BY:

SELECT * FROM employees ORDER BY commission_pct NULLS LAST;

NULL и ROWNUM:

SELECT * FROM employees WHERE commission_pct IS NULL AND ROWNUM <= 5;

NULL в агрегатах:

SELECT COUNT(commission_pct) FROM employees;

COUNT(*) учитывает NULL:

SELECT COUNT(*) FROM employees;

COUNT(DISTINCT …) без NULL:

SELECT COUNT(DISTINCT commission_pct) FROM employees;

SUM без NULL:

SELECT SUM(commission_pct) FROM employees;

AVG без NULL:

SELECT AVG(commission_pct) FROM employees;

MAX/MIN с NULL:

SELECT MAX(commission_pct), MIN(commission_pct) FROM employees;

NULL в аналитике:

SELECT commission_pct, RANK() OVER (ORDER BY commission_pct) FROM employees;

CASE и NULLIF:

SELECT CASE WHEN NULLIF(manager_id, 0) IS NULL THEN 'НЕТ' ELSE 'ЕСТЬ' END FROM employees;

NULL с TO_CHAR:

SELECT TO_CHAR(NULL) FROM dual;

NVL в PL/SQL:

DECLARE x NUMBER := NULL;
BEGIN
  DBMS_OUTPUT.PUT_LINE(NVL(x, 0));
END;

NULL и DUAL:

SELECT NULL FROM dual;

NULL в PIVOT:

SELECT * FROM (
  SELECT department_id, job_id, commission_pct FROM employees
)
PIVOT (MAX(commission_pct) FOR job_id IN ('SA_REP', 'IT_PROG'));

NULL и JSON_VALUE:

SELECT JSON_VALUE(data, '$.bonus') FROM employees_json WHERE JSON_VALUE(data, '$.bonus') IS NULL;

NULL и JSON_TABLE:

SELECT * FROM JSON_TABLE(
  '{"data":[{"id":1,"bonus":null}]}'
  , '$.data[*]'
  COLUMNS (id NUMBER PATH '$.id', bonus NUMBER PATH '$.bonus')
) WHERE bonus IS NULL;

NULL и IS NOT DISTINCT FROM (Oracle 23+):

SELECT * FROM employees WHERE commission_pct IS NOT DISTINCT FROM NULL;

NULL в VIEW:

CREATE OR REPLACE VIEW v_null_bonus AS
SELECT * FROM employees WHERE commission_pct IS NULL;

NULL в INLINE VIEW:

SELECT * FROM (
  SELECT * FROM employees WHERE commission_pct IS NULL
);

NULL и INITCAP:

SELECT INITCAP(NULL) FROM dual;

NULL в аналитике DENSE_RANK:

SELECT first_name, DENSE_RANK() OVER (ORDER BY commission_pct) FROM employees;

NULL и RATIO_TO_REPORT:

SELECT RATIO_TO_REPORT(commission_pct) OVER () FROM employees;

NULL и MEDIAN:

SELECT MEDIAN(commission_pct) OVER () FROM employees;

NULL и PERCENT_RANK:

SELECT PERCENT_RANK() OVER (ORDER BY commission_pct) FROM employees;

NULL и ROW_NUMBER:

SELECT ROW_NUMBER() OVER (ORDER BY NVL(commission_pct, 0)) FROM employees;

NULL и GROUPING SETS:

SELECT department_id, COUNT(*) FROM employees
GROUP BY GROUPING SETS ((department_id), ())
ORDER BY department_id NULLS LAST;

NULL в MERGE:

MERGE INTO employees e USING dual d
ON (e.commission_pct IS NULL)
WHEN MATCHED THEN UPDATE SET salary = salary + 100;

NULL в DELETE:

DELETE FROM employees WHERE commission_pct IS NULL;

NULL в UPDATE:

UPDATE employees SET bonus = 0 WHERE bonus IS NULL;

NULL и CASE в SELECT:

SELECT CASE commission_pct WHEN NULL THEN 'Нет' ELSE 'Да' END FROM employees;

NULL и LENGTH:

SELECT LENGTH(NULL) FROM dual;

NULL и CAST:

SELECT CAST(NULL AS VARCHAR2(10)) FROM dual;

NULL и REGEXP_LIKE:

SELECT * FROM employees WHERE NOT REGEXP_LIKE(NVL(commission_pct, 0), '[^0-9.]');

NULL и REPLACE:

SELECT REPLACE(NULL, 'A', 'B') FROM dual;

NULL и TRIM:

SELECT TRIM(NULL) FROM dual;

NULL в аналитике WITHIN GROUP:

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY commission_pct) FROM employees;

NULL и FILTER:

SELECT COUNT(*) FILTER (WHERE commission_pct IS NULL) AS nulls FROM employees;

NULL и аналитика в подзапросе:

SELECT * FROM employees e
WHERE commission_pct IS NULL
AND EXISTS (
  SELECT 1 FROM (
    SELECT employee_id, RANK() OVER (ORDER BY salary) AS rnk FROM employees
  ) r WHERE r.employee_id = e.employee_id
);

NULL и соединения:

SELECT e.first_name, d.department_name FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name IS NULL;

📆 Заключение: как правильно использовать NULL в Oracle

Работа с NULL требует понимания его уникальности. Это не «значение по умолчанию», а индикатор отсутствия данных. Игнорирование особенностей NULL может привести к неожиданным результатам в фильтрации, логике и расчётах.

Используй специальные операторы (IS NULL, NVL, COALESCE, NULLIF), чтобы точно контролировать поведение запроса и корректно обрабатывать неопределённые значения.