O'Reilly logo

SQL in a Nutshell, 2nd Edition by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required