EXCEPT and EXCEPT ALL: New Set Operators in Oracle 23ai

If you’ve ever had to port SQL from PostgreSQL or SQL Server to Oracle, you’ve probably hit this wall: Oracle uses MINUS where every other database uses EXCEPT. They do the same thing, but the name difference forces you to rewrite queries or maintain separate versions.

Oracle 23ai adds EXCEPT and EXCEPT ALL as aliases and extensions to the existing set operator vocabulary.

EXCEPT — equivalent to MINUS (distinct rows only):

-- Customers who placed orders in 2024 but NOT in 2025
SELECT customer_id FROM orders WHERE order_year = 2024
EXCEPT
SELECT customer_id FROM orders WHERE order_year = 2025;

This is identical to using MINUS. Both are now valid.

EXCEPT ALL — the new addition:

-- Returns all rows from the first query not in the second,
-- including duplicates
SELECT product_id FROM inventory_snapshot_jan
EXCEPT ALL
SELECT product_id FROM inventory_snapshot_feb;

EXCEPT ALL does not eliminate duplicates from either side before comparing. If a value appears 3 times in the first set and 1 time in the second, the result contains 2 occurrences. This is the behavior defined in ANSI SQL and already present in PostgreSQL.

INTERSECT ALL is also new:

Oracle 23ai also adds INTERSECT ALL for symmetric completeness — it returns matching rows including duplicates, preserving the lower count from either side.

Complete set operator list in Oracle 23ai:

For cross-database SQL portability alone, EXCEPT is a welcome addition.