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.