SQL in a Nutshell, 3rd Edition

Name

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.”

Platform	Command
MySQL	Not supported
Oracle	Supported, with limitations
PostgreSQL	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[, ...])}
EXCEPT [ALL | DISTINCT]
[CORRESPONDING [BY (column1, colum2, ...)]]
{SELECT statement2 | VALUES (expr2[, ...])}
EXCEPT [ALL | DISTINCT]
[CORRESPONDING [BY (column1, column2, ...)]]
...

Keywords

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. ...