NOT

📖 Описание

Oracle условие NOT (также называется NOT оператор) используется для отрицания условия в SELECT, UPDATE, DELETE и исходном SELECT оператора INSERT.


🧩 Синтаксис

Синтаксис условия NOT в Oracle/PLSQL:

NOT condition

⚙️ Параметры и аргументы

condition Условие, результат которого нужно инвертировать.


📝 Примечание

  • NOT меняет TRUE на FALSE и FALSE на TRUE; UNKNOWN остаётся UNKNOWN.

WHERE оставляет только TRUE: отрицание UNKNOWN не превращает неизвестное сравнение в подходящую строку.

💻 NOT с IN

WITH studios (studio_id, studio_name, city, status, kind, capacity, fee) AS (
  SELECT 11, 'Clay Room', 'Tashkent', 'Active', 'CLAY', 8, 120 FROM dual
  UNION ALL SELECT 12, 'Paper House', 'Samarkand', 'Active', 'PAPER', 12, 90 FROM dual
  UNION ALL SELECT 13, 'Wood Lab', 'Tashkent', 'Draft', 'WOOD', 6, 200 FROM dual
  UNION ALL SELECT 14, 'Clay Room', 'Bukhara', 'Active', 'CLAY', 10, NULL FROM dual
  UNION ALL SELECT 15, 'Glass Studio', 'Samarkand', NULL, 'GLASS', NULL, 160 FROM dual
  UNION ALL SELECT 16, 'Paper House', 'Nukus', 'Draft', 'PAPER', 4, 60 FROM dual
  UNION ALL SELECT 17, 'Canvas Room', 'Tashkent', 'Active', 'CANVAS', 16, 250 FROM dual
  UNION ALL SELECT 18, NULL, NULL, NULL, NULL, NULL, NULL FROM dual
)
SELECT studio_id, studio_name FROM studios
WHERE studio_name NOT IN ('Clay Room', 'Paper House')
ORDER BY studio_id;
STUDIO_ID STUDIO_NAME
13 Wood Lab
15 Glass Studio
17 Canvas Room

NULL в имени студии 18 не проходит условие. Если добавить NULL в этот одномерный список NOT IN, ни одна строка не даст TRUE. Выбор между положительным и отрицательным условием определяется смыслом, а скорость — планом и данными.

💻 NOT с IS NULL

WITH studios (studio_id, studio_name, city, status, kind, capacity, fee) AS (
  SELECT 11, 'Clay Room', 'Tashkent', 'Active', 'CLAY', 8, 120 FROM dual
  UNION ALL SELECT 12, 'Paper House', 'Samarkand', 'Active', 'PAPER', 12, 90 FROM dual
  UNION ALL SELECT 13, 'Wood Lab', 'Tashkent', 'Draft', 'WOOD', 6, 200 FROM dual
  UNION ALL SELECT 14, 'Clay Room', 'Bukhara', 'Active', 'CLAY', 10, NULL FROM dual
  UNION ALL SELECT 15, 'Glass Studio', 'Samarkand', NULL, 'GLASS', NULL, 160 FROM dual
  UNION ALL SELECT 16, 'Paper House', 'Nukus', 'Draft', 'PAPER', 4, 60 FROM dual
  UNION ALL SELECT 17, 'Canvas Room', 'Tashkent', 'Active', 'CANVAS', 16, 250 FROM dual
  UNION ALL SELECT 18, NULL, NULL, NULL, NULL, NULL, NULL FROM dual
)
SELECT studio_id, status FROM studios
WHERE status IS NOT NULL
ORDER BY studio_id;
STUDIO_ID STATUS
11 Active
12 Active
13 Draft
14 Active
16 Draft
17 Active

IS NOT NULL эквивалентно NOT (status IS NULL).

💻 NOT с LIKE

WITH studios (studio_id, studio_name, city, status, kind, capacity, fee) AS (
  SELECT 11, 'Clay Room', 'Tashkent', 'Active', 'CLAY', 8, 120 FROM dual
  UNION ALL SELECT 12, 'Paper House', 'Samarkand', 'Active', 'PAPER', 12, 90 FROM dual
  UNION ALL SELECT 13, 'Wood Lab', 'Tashkent', 'Draft', 'WOOD', 6, 200 FROM dual
  UNION ALL SELECT 14, 'Clay Room', 'Bukhara', 'Active', 'CLAY', 10, NULL FROM dual
  UNION ALL SELECT 15, 'Glass Studio', 'Samarkand', NULL, 'GLASS', NULL, 160 FROM dual
  UNION ALL SELECT 16, 'Paper House', 'Nukus', 'Draft', 'PAPER', 4, 60 FROM dual
  UNION ALL SELECT 17, 'Canvas Room', 'Tashkent', 'Active', 'CANVAS', 16, 250 FROM dual
  UNION ALL SELECT 18, NULL, NULL, NULL, NULL, NULL, NULL FROM dual
)
SELECT studio_id, studio_name FROM studios
WHERE studio_name NOT LIKE 'C%'
ORDER BY studio_id;
STUDIO_ID STUDIO_NAME
12 Paper House
13 Wood Lab
15 Glass Studio
16 Paper House

При сопоставлении с учётом регистра исключаются названия на C, а также NULL. См. LIKE.

💻 NOT с BETWEEN

WITH studios (studio_id, studio_name, city, status, kind, capacity, fee) AS (
  SELECT 11, 'Clay Room', 'Tashkent', 'Active', 'CLAY', 8, 120 FROM dual
  UNION ALL SELECT 12, 'Paper House', 'Samarkand', 'Active', 'PAPER', 12, 90 FROM dual
  UNION ALL SELECT 13, 'Wood Lab', 'Tashkent', 'Draft', 'WOOD', 6, 200 FROM dual
  UNION ALL SELECT 14, 'Clay Room', 'Bukhara', 'Active', 'CLAY', 10, NULL FROM dual
  UNION ALL SELECT 15, 'Glass Studio', 'Samarkand', NULL, 'GLASS', NULL, 160 FROM dual
  UNION ALL SELECT 16, 'Paper House', 'Nukus', 'Draft', 'PAPER', 4, 60 FROM dual
  UNION ALL SELECT 17, 'Canvas Room', 'Tashkent', 'Active', 'CANVAS', 16, 250 FROM dual
  UNION ALL SELECT 18, NULL, NULL, NULL, NULL, NULL, NULL FROM dual
)
SELECT studio_id, studio_name FROM studios
WHERE studio_id NOT BETWEEN 12 AND 16
ORDER BY studio_id;
STUDIO_ID STUDIO_NAME
11 Clay Room
17 Canvas Room
18 NULL

Границы 12 и 16 входят в исключаемый диапазон. Имя NULL не мешает строке 18: проверяется её ненулевой идентификатор. Эквивалентный запрос:

WITH studios (studio_id, studio_name, city, status, kind, capacity, fee) AS (
  SELECT 11, 'Clay Room', 'Tashkent', 'Active', 'CLAY', 8, 120 FROM dual
  UNION ALL SELECT 12, 'Paper House', 'Samarkand', 'Active', 'PAPER', 12, 90 FROM dual
  UNION ALL SELECT 13, 'Wood Lab', 'Tashkent', 'Draft', 'WOOD', 6, 200 FROM dual
  UNION ALL SELECT 14, 'Clay Room', 'Bukhara', 'Active', 'CLAY', 10, NULL FROM dual
  UNION ALL SELECT 15, 'Glass Studio', 'Samarkand', NULL, 'GLASS', NULL, 160 FROM dual
  UNION ALL SELECT 16, 'Paper House', 'Nukus', 'Draft', 'PAPER', 4, 60 FROM dual
  UNION ALL SELECT 17, 'Canvas Room', 'Tashkent', 'Active', 'CANVAS', 16, 250 FROM dual
  UNION ALL SELECT 18, NULL, NULL, NULL, NULL, NULL, NULL FROM dual
)
SELECT studio_id, studio_name FROM studios
WHERE studio_id < 12 OR studio_id > 16
ORDER BY studio_id;

💻 NOT с EXISTS

WITH studios (studio_id, studio_name, city, status, kind, capacity, fee) AS (
  SELECT 11, 'Clay Room', 'Tashkent', 'Active', 'CLAY', 8, 120 FROM dual
  UNION ALL SELECT 12, 'Paper House', 'Samarkand', 'Active', 'PAPER', 12, 90 FROM dual
  UNION ALL SELECT 13, 'Wood Lab', 'Tashkent', 'Draft', 'WOOD', 6, 200 FROM dual
  UNION ALL SELECT 14, 'Clay Room', 'Bukhara', 'Active', 'CLAY', 10, NULL FROM dual
  UNION ALL SELECT 15, 'Glass Studio', 'Samarkand', NULL, 'GLASS', NULL, 160 FROM dual
  UNION ALL SELECT 16, 'Paper House', 'Nukus', 'Draft', 'PAPER', 4, 60 FROM dual
  UNION ALL SELECT 17, 'Canvas Room', 'Tashkent', 'Active', 'CANVAS', 16, 250 FROM dual
  UNION ALL SELECT 18, NULL, NULL, NULL, NULL, NULL, NULL FROM dual
),
bookings (booking_id, studio_id) AS (
  SELECT 101, 11 FROM dual
  UNION ALL SELECT 102, 11 FROM dual
  UNION ALL SELECT 103, 14 FROM dual
  UNION ALL SELECT 104, 17 FROM dual
)
SELECT s.studio_id, s.studio_name FROM studios s
WHERE NOT EXISTS (
  SELECT 1 FROM bookings b WHERE b.studio_id = s.studio_id
)
ORDER BY s.studio_id;
STUDIO_ID STUDIO_NAME
12 Paper House
13 Wood Lab
15 Glass Studio
16 Paper House
18 NULL

NOT EXISTS выбирает студии без совпавшего бронирования. В этом примере неизвестное название не влияет на связь по studio_id.

См. документацию Oracle: Logical-Conditions.

См. документацию Oracle: IN-Condition.