EXCEPT
EXCEPT is like the UNION statement, except that EXCEPT produces rows that result from the first query but not the second.
Note: EXCEPT is known as MINUS in Oracle.
Syntax
<query_expression>
EXCEPT [ALL]
<query_expression>
Remarks
• Unlike UNION and INTERSECT, EXCEPT is not commutative. That is, A EXCEPT B is not the same as B EXCEPT A. Otherwise, the rules are the same as for UNION.
• When you use EXCEPT ALL, if a row appears x times in the first table and y times in the second table, it appears z times in the result table, where z is x - y or 0 (zero), whichever is greater.
• EXCEPT is similar to EXCEPT ALL and eliminates the duplicates.
• Using only EXCEPT provides results that have no duplicates in their result set.
• Using EXCEPT ALL includes rows that have duplicate values.
Example (EXCEPT)
The following query on a file in the Studio resource tree lists the cities where suppliers live but no customers live.
SELECT City
FROM /shared/examples/ds_inventory/suppliers
EXCEPT
SELECT City
FROM /shared/examples/ds_orders/customers
Oakland is the only city in the supplier’s result set that is not in the customers result set.
Example (EXCEPT ALL)
SELECT City
FROM /shared/examples/ds_inventory/suppliers
EXCEPT ALL
SELECT City
FROM /shared/examples/ds_orders/customers
Adding ALL returns rows that have duplicates in the suppliers result set.