EXCEPT Set Operator

The EXCEPT set operator retrieves the result sets of two or more queries, including all the records retrieved by the first query that are not also found in subsequent queries. Whereas JOIN clauses are used to return the rows of two or more queries that are in common, EXCEPT is used to filter out the records that are present in only one of multiple, but similar, tables.

EXCEPT is in a class of keywords called set operators. Other set operators include INTERSECT and UNION. (MINUS is Oracle’s equivilent to the EXCEPT keyword; EXCEPT is the ANSI standard.) All set operators are used to simultaneously manipulate the result sets of two or more queries, hence the term “set operators.”




Not supported


Supported, with limitations


Supported, with limitations

SQL Server

Supported, with limitations

SQL2003 Syntax

There are technically no limits to the number of queries that you may combine with the EXCEPT operator. The general syntax is:

{SELECT statement1 | VALUES (expr1[, ...])}
[CORRESPONDING [BY (column1, colum2, ...)]]
{SELECT statement2 | VALUES (expr2[, ...])}
[CORRESPONDING [BY (column1, column2, ...)]]


VALUES (expr1[, . . . ])

Generates a derived result set with explicitly declared values as expr1, expr2, etc. It is essentially a SELECT statement result set without the SELECT . . . FROM syntax. This is known as a row constructor, since the rows of the result set are manually constructed. ...

Get SQL in a Nutshell, 3rd Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.