IS в Oracle SQL — как сравнивать и проверять NULL

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

Оператор IS в Oracle SQL используется для проверки значений, прежде всего NULL, а также для логических сравнений (IS TRUE, IS FALSE) и проверки типа объектов (IS OF TYPE). Эта статья покажет, как и где применять IS, с 50 практическими примерами.

🖋️ Синтаксис оператора IS

SELECT * FROM таблица
WHERE столбец IS NULL;

Другие формы:

IS NOT NULL
IS TRUE / IS FALSE
IS OF TYPE (в объектно-ориентированных типах)

🔄 Где используется IS

Проверка наличия или отсутствия значений
Логические сравнения в PL/SQL
Проверка объекта на тип данных
Контроль бизнес-логики и корректности данных

📊 Примеры использования IS в Oracle SQL

IS NULL:

SELECT * FROM employees WHERE manager_id IS NULL;

IS NOT NULL:

SELECT * FROM employees WHERE department_id IS NOT NULL;

IS в SELECT с подстановкой:

SELECT employee_id, manager_id, CASE WHEN manager_id IS NULL THEN 'Нет менеджера' ELSE 'Есть менеджер' END AS статус FROM employees;

IS TRUE в PL/SQL:

DECLARE
  v_active BOOLEAN := TRUE;
BEGIN
  IF v_active IS TRUE THEN
    DBMS_OUTPUT.PUT_LINE('Активен');
  END IF;
END;

IS FALSE в PL/SQL:

DECLARE
  v_flag BOOLEAN := FALSE;
BEGIN
  IF v_flag IS FALSE THEN
    DBMS_OUTPUT.PUT_LINE('Флаг отключён');
  END IF;
END;

IS NULL с подзапросом:

SELECT * FROM employees WHERE department_id IS NULL
AND employee_id IN (SELECT employee_id FROM job_history);

IS NOT NULL с функцией:

SELECT * FROM employees WHERE NVL(commission_pct, 0) IS NOT NULL;

IS NULL с OUTER JOIN:

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

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

SELECT * FROM (
  SELECT employee_id, RANK() OVER (ORDER BY hire_date) AS rnk FROM employees
) WHERE rnk IS NOT NULL;

IS NULL с DELETE:

DELETE FROM employees WHERE commission_pct IS NULL;

IS NOT NULL в UPDATE:

UPDATE employees SET salary = salary + 1000 WHERE commission_pct IS NOT NULL;

IS NULL с CASE:

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

IS OF TYPE в объектных таблицах:

SELECT * FROM people WHERE person IS OF (student_t);

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

SELECT * FROM employees e WHERE EXISTS (
  SELECT 1 FROM departments d WHERE d.manager_id IS NULL AND d.department_id = e.department_id
);

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

SELECT * FROM (
  SELECT employee_id, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dr
  FROM employees
) WHERE dr IS NOT NULL;

IS NULL с JSON_VALUE:

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

IS NOT NULL с JSON_TABLE:

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

IS NULL в INLINE VIEW:

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

IS TRUE в PL/SQL переменной:

DECLARE
  v_flag BOOLEAN := TRUE;
BEGIN
  IF v_flag IS TRUE THEN
    DBMS_OUTPUT.PUT_LINE('Флаг активен');
  END IF;
END;

IS FALSE в IF-блоке:

DECLARE
  v_debug BOOLEAN := FALSE;
BEGIN
  IF v_debug IS FALSE THEN
    DBMS_OUTPUT.PUT_LINE('Режим отладки выключен');
  END IF;
END;

IS NULL с LENGTH:

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

IS NOT NULL в аналитике NTILE:

SELECT * FROM (
  SELECT *, NTILE(4) OVER (ORDER BY salary) AS nt FROM employees
) WHERE nt IS NOT NULL;

IS NULL в MERGE:

MERGE INTO employees e USING new_employees n
ON (e.employee_id = n.employee_id AND e.commission_pct IS NULL)
WHEN MATCHED THEN UPDATE SET e.salary = n.salary;

IS NULL с функцией DUMP:

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

IS NULL с SYS_CONTEXT:

SELECT * FROM employees WHERE SYS_CONTEXT('USERENV','CURRENT_SCHEMA') IS NULL;

IS NOT NULL с CAST:

SELECT * FROM employees WHERE CAST(manager_id AS VARCHAR2(10)) IS NOT NULL;

IS NULL в фильтрации агрегатов:

SELECT department_id FROM employees
GROUP BY department_id
HAVING SUM(salary) IS NULL;

IS NOT NULL с NVL:

SELECT * FROM employees WHERE NVL(manager_id, 0) IS NOT NULL;

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

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM employees
) WHERE rn IS NOT NULL;

IS NULL с TRIM:

SELECT * FROM employees WHERE TRIM(first_name) IS NULL;

IS NOT NULL с INITCAP:

SELECT * FROM employees WHERE INITCAP(last_name) IS NOT NULL;

IS NULL с подстановкой:

SELECT * FROM employees WHERE first_name IS NULL;

IS TRUE в логике авторизации:

DECLARE
  v_auth BOOLEAN := TRUE;
BEGIN
  IF v_auth IS TRUE THEN
    DBMS_OUTPUT.PUT_LINE('Доступ разрешён');
  END IF;
END;

IS FALSE в блоке обработки:

DECLARE
  v_check BOOLEAN := FALSE;
BEGIN
  IF v_check IS FALSE THEN
    DBMS_OUTPUT.PUT_LINE('Флаг отклонён');
  END IF;
END;

IS NOT NULL с ROWNUM:

SELECT * FROM employees WHERE ROWNUM IS NOT NULL;

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

SELECT first_name,
  CASE
    WHEN commission_pct IS NULL THEN 'Нет комиссионных'
    ELSE 'Есть комиссионные'
  END AS статус
FROM employees;

IS NOT NULL с аналитикой RANK:

SELECT * FROM (
  SELECT *, RANK() OVER (ORDER BY salary) AS rnk FROM employees
) WHERE rnk IS NOT NULL;

IS NULL в JSON_TABLE:

SELECT * FROM JSON_TABLE(
  '{"employees": [{"id":1,"phone":null},{"id":2,"phone":"123"}]}'
  , '$.employees[*]'
  COLUMNS (id NUMBER PATH '$.id', phone VARCHAR2(10) PATH '$.phone')
) WHERE phone IS NULL;

IS NOT NULL с фильтрацией даты:

SELECT * FROM employees WHERE hire_date IS NOT NULL;

IS NULL с CONCAT:

SELECT * FROM employees WHERE CONCAT(first_name, last_name) IS NULL;

IS NULL с FILTER:

SELECT department_id, COUNT(*) FILTER (WHERE manager_id IS NULL) AS без_менеджера
FROM employees GROUP BY department_id;

IS TRUE с флагом в SELECT:

SELECT employee_id, salary > 5000 AS высокий_доход FROM employees WHERE salary > 5000 IS TRUE;

IS NOT NULL в массиве:

SELECT * FROM TABLE(CAST(my_array AS my_array_type)) WHERE COLUMN_VALUE IS NOT NULL;

IS NULL с PIVOT:

SELECT * FROM (
  SELECT department_id, job_id, salary FROM employees
)
PIVOT (
  SUM(salary) FOR job_id IN ('IT_PROG', 'HR')
) WHERE HR IS NULL;

IS NOT NULL в аналитике MEDIAN:

SELECT * FROM (
  SELECT *, MEDIAN(salary) OVER () AS med FROM employees
) WHERE med IS NOT NULL;

IS NOT NULL с REPLACE:

SELECT * FROM employees WHERE REPLACE(last_name, 'a', '') IS NOT NULL;

IS NULL с EXTRACT:

SELECT * FROM employees WHERE EXTRACT(YEAR FROM hire_date) IS NULL;

IS NOT NULL в аналитике PERCENT_RANK:

SELECT * FROM (
  SELECT *, PERCENT_RANK() OVER (ORDER BY salary) AS pr FROM employees
) WHERE pr IS NOT NULL;

IS NOT NULL с функцией LENGTH:

SELECT * FROM employees WHERE LENGTH(email) IS NOT NULL;

IS NULL и NULLIF:

SELECT * FROM employees WHERE NULLIF(commission_pct, 0) IS NULL;

📆 Заключение: зачем изучать IS в Oracle SQL

Оператор IS — это надёжный способ сравнивать с NULL и проверять логические и типовые значения. Он критичен в логике фильтрации и управления данными, особенно при работе с неопределёнными значениями и булевыми условиями.

Зная IS, ты избежишь типичных ошибок и сможешь правильно интерпретировать пропущенные данные, флаги и типы объектов.