MINUS operator

When two queries are combined by this set operator, the MINUS operator calculates the set difference between the rows returned by the SELECT statement on the left side and the rows returned by the SELECT statement on the right side.

The MINUS operator returns only the rows that are present in first result set but that are not in the second set. The MINUS results are always distinct or unique rows, because MINUS eliminates any duplicate rows.

For the same data set that is listed in the INTERSECT Operator topic, the following query returns all the distinct rows from result set of the query to the left of the MINUS operator that are not in the result set of the query on the right:

SELECT col1 FROM t1 MINUS SELECT col1 FROM t2;

       col1 

          2

1 row(s) retrieved.

The MINUS operator has some (but not all) of the same restrictions as the UNION operator, but MINUS does not support the ALL keyword that enables UNION to return duplicate values. See also the topic Restrictions on a Combined SELECT.