O'Reilly logo

SQL in a Nutshell, 3rd 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

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

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