SQL in a Nutshell, 2nd Edition

Name

EXCEPT Set Operator

Synopsis

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 a set operator. 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 set of two or more queries, hence the term “set operators.”

Platform	Command
DB2	Supported, with limitations
MySQL	Not supported
Oracle	Supported,with limitations
PostgreSQL	Supported, with limitations
SQL Server	Not supported

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, column2, ...)] ]
{SELECT statement2 | VALUES (expr2 [,...] ) }
EXCEPT [ALL | DISTINCT]
[CORRESPONDING [BY (column1, column2, ...)] ]
...

Keywords

VALUES (expr1 [,...] )

Generates a derived result set with explicitly declared values as expression1, expression2, etc. It is essentially a SELECT statement result set without the SELECT...FROM syntax. This is known as a