NOT в Oracle SQL — 50 примеров исключения данных

NOT в Oracle SQL — логический оператор, позволяющий исключить из выборки строки, соответствующие определённому условию. Он применяется перед другими логическими выражениями, инвертируя их результат. В этой статье мы покажем, как использовать NOT с IN, EXISTS, LIKE, BETWEEN, сравнениями, подзапросами и фильтрацией. Внутри — 50 примеров.

🖋️ Синтаксис логического оператора NOT

SELECT [столбцы | *]
FROM [таблица]
WHERE NOT (условие)

NOT применяется перед условием или логическим выражением и возвращает TRUE, если выражение ложно.

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

• Исключение по спискам и диапазонам
• Отрицание наличия в подзапросе
• Инверсия логических выражений
• Альтернатива сложным условиям
• Фильтрация NULL и LIKE

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

1. NOT с равенством:

SELECT * FROM employees WHERE NOT department_id = 10;

2. NOT с IN:

SELECT * FROM employees WHERE job_id NOT IN ('IT_PROG', 'SA_REP');

3. NOT с BETWEEN:

SELECT * FROM employees WHERE salary NOT BETWEEN 3000 AND 8000;

4. NOT с LIKE:

SELECT * FROM employees WHERE first_name NOT LIKE 'A%';

5. NOT с IS NULL:

SELECT * FROM employees WHERE NOT manager_id IS NULL;

6. NOT с логическим выражением:

SELECT * FROM employees WHERE NOT (salary > 5000 AND department_id = 60);

7. NOT с EXISTS:

SELECT * FROM departments d WHERE NOT EXISTS (
  SELECT 1 FROM employees e WHERE e.department_id = d.department_id
);

8. NOT с подзапросом:

SELECT * FROM employees WHERE NOT salary > (
  SELECT AVG(salary) FROM employees
);

9. NOT с IS NOT NULL:

SELECT * FROM employees WHERE NOT commission_pct IS NOT NULL;

10. NOT с REGEXP_LIKE:

SELECT * FROM employees WHERE NOT REGEXP_LIKE(first_name, '^A');

11. NOT в UPDATE:

UPDATE employees SET salary = salary * 1.05 WHERE NOT department_id = 50;

12. NOT в DELETE:

DELETE FROM employees WHERE NOT job_id = 'SA_MAN';

13. NOT с LENGTH:

SELECT * FROM employees WHERE NOT LENGTH(first_name) > 5;

14. NOT с AND внутри:

SELECT * FROM employees WHERE NOT (department_id = 60 AND salary < 3000);

15. NOT с OR внутри:

SELECT * FROM employees WHERE NOT (job_id = 'IT_PROG' OR department_id = 80);

16. NOT с TO_CHAR:

SELECT * FROM employees WHERE NOT TO_CHAR(hire_date, 'YYYY') = '2023';

17. NOT с LOWER:

SELECT * FROM employees WHERE NOT LOWER(last_name) = 'smith';

18. NOT с UPPER:

SELECT * FROM employees WHERE NOT UPPER(job_id) = 'HR';

19. NOT с IN и подзапросом:

SELECT * FROM employees WHERE department_id NOT IN (
  SELECT department_id FROM departments WHERE location_id = 1700
);

20. NOT с аналитикой:

SELECT * FROM (
  SELECT *, RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees
) WHERE NOT rnk <= 3;

21. NOT с CASE:

SELECT * FROM employees WHERE NOT (
  CASE WHEN salary > 7000 THEN 'HIGH' ELSE 'LOW' END = 'HIGH'
);

22. NOT с COALESCE:

SELECT * FROM employees WHERE NOT COALESCE(manager_id, 0) = 101;

23. NOT с NVL:

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

24. NOT с BETWEEN и датами:

SELECT * FROM employees WHERE NOT hire_date BETWEEN TO_DATE('2020-01-01', 'YYYY-MM-DD') AND SYSDATE;

25. NOT с LIKE и ESCAPE:

SELECT * FROM employees WHERE NOT first_name LIKE 'A\_%' ESCAPE '\';

26. NOT с аналитической функцией:

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

27. NOT с TO_DATE:

SELECT * FROM employees WHERE NOT hire_date < TO_DATE('2022-01-01', 'YYYY-MM-DD');

28. NOT в WITH:

WITH filter AS (
  SELECT * FROM employees WHERE NOT salary BETWEEN 2000 AND 5000
)
SELECT * FROM filter;

29. NOT с ROUND:

SELECT * FROM employees WHERE NOT ROUND(salary, -3) = 6000;

30. NOT с модулем MOD:

SELECT * FROM employees WHERE NOT MOD(salary, 1000) = 0;

31. NOT с NULLIF:

SELECT * FROM employees WHERE NOT NULLIF(salary, 0) = salary;

32. NOT в VIEW:

CREATE OR REPLACE VIEW v_filter AS
SELECT * FROM employees WHERE NOT department_id = 50;

33. NOT с SYS_CONTEXT:

SELECT * FROM employees WHERE NOT SYS_CONTEXT('USERENV', 'SESSION_USER') = 'HR';

34. NOT с SUBSTR:

SELECT * FROM employees WHERE NOT SUBSTR(first_name, 1, 1) = 'J';

35. NOT с JSON_VALUE:

SELECT * FROM employees_json WHERE NOT JSON_VALUE(data, '$.active') = 'true';

36. NOT с JSON_TABLE:

SELECT * FROM JSON_TABLE(
  '{"employees": [{"id":1,"role":"HR"},{"id":2,"role":"IT"}]}'
  , '$.employees[*]'
  COLUMNS (id NUMBER PATH '$.id', role VARCHAR2(10) PATH '$.role')
) WHERE NOT role = 'HR';

37. NOT с INLINE VIEW:

SELECT * FROM (
  SELECT * FROM employees WHERE salary > 5000
) WHERE NOT department_id = 90;

38. NOT с аналитикой ROW_NUMBER:

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
  FROM employees
) WHERE NOT rn = 1;

39. NOT с dual:

SELECT 'ok' FROM dual WHERE NOT 1=0;

40. NOT с скалярным подзапросом:

SELECT * FROM employees WHERE NOT department_id = (
  SELECT department_id FROM departments WHERE department_name = 'IT'
);

41. NOT с ROWNUM:

SELECT * FROM employees WHERE NOT ROWNUM <= 3;

42. NOT с DELETE:

DELETE FROM employees WHERE NOT (department_id = 60 AND job_id = 'SA_REP');

43. NOT с аналитикой и HAVING:

SELECT department_id, COUNT(*) FROM employees
GROUP BY department_id
HAVING NOT COUNT(*) > 5;

44. NOT с CASE и датой:

SELECT * FROM employees WHERE NOT (
  CASE WHEN hire_date < SYSDATE - 365 THEN 'OLD' ELSE 'NEW' END = 'OLD'
);

45. NOT с UNION ALL:

SELECT * FROM employees WHERE NOT job_id = 'IT_PROG'
UNION ALL
SELECT * FROM employees WHERE NOT job_id = 'SA_MAN';

46. NOT с LENGTH:

SELECT * FROM employees WHERE NOT LENGTH(last_name) = 5;

47. NOT с DUMP:

SELECT * FROM employees WHERE NOT DUMP(job_id) LIKE '%73%';

48. NOT в MERGE:

MERGE INTO employees e USING new_employees n
ON (NOT e.employee_id = n.employee_id)
WHEN NOT MATCHED THEN INSERT (employee_id, job_id) VALUES (n.employee_id, n.job_id);

49. NOT с аналитикой и RANK:

SELECT * FROM (
  SELECT *, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
  FROM employees
) WHERE NOT rnk <= 2;

50. NOT с фильтрацией по длине строки:

SELECT * FROM employees WHERE NOT LENGTH(first_name) = 4;

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

Оператор NOT — мощный инструмент для обратной логики в запросах. Он позволяет исключать ненужные данные, уточнять фильтры и формулировать условия, невозможные через прямые выражения.

Зная NOT, ты сможешь строить гибкие и точные выборки, комбинируя отрицания с любыми условиями в WHERE.

🕸️ Следующая статья:

IN в Oracle SQL — как проверять наличие в списке