The MINUS operator returns all rows from the left SELECT result that do not appear in the right SELECT result (the set difference). Duplicate rows are automatically removed from the output.
Syntax
select_statement MINUS select_statementEach select_statement is a SELECT statement. Neither statement can contain an ORDER BY or FOR UPDATE clause.
Behavior
MINUS computes the rows that exist in the left query but not in the right query. The result never contains duplicate rows, regardless of how many times a row appears in the left query.
Example: Find employee IDs that appear in the all_employees table but not in the active_employees table.
SELECT employee_id FROM all_employees
MINUS
SELECT employee_id FROM active_employees;| EMPLOYEE_ID |
|---|
| 1023 |
| 1047 |
| 1091 |
Evaluation order
When a query contains multiple MINUS operators, they are evaluated left to right unless parentheses override the order. MINUS binds at the same precedence level as UNION.
-- Evaluated as: (query1 MINUS query2) MINUS query3
query1 MINUS query2 MINUS query3
-- Use parentheses to change the order
query1 MINUS (query2 MINUS query3)