SQL in a Nutshell, 3rd Edition

Name

INTERSECT Set Operator

The INTERSECT set operator retrieves the rows of two or more queries, where the rows of the result sets are identical in both the first and second (and possibly more) queries. In some ways, INTERSECT is a lot like an INNER JOIN operation (see the JOIN section for details).

INTERSECT is in a class of keywords called set operators. Other set operators include EXCEPT and UNION. 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 is technically no limit to the number of queries that you may combine with the INTERSECT set operator. The general syntax is:

<SELECT statement1>
INTERSECT [ALL | DISTINCT]
[CORRESPONDING [BY (column1, column2, ...)]]
<SELECT statement2>
INTERSECT [ALL | DISTINCT]
[CORRESPONDING [BY (column1, column2, ...)]]
...

Keywords

ALL

Includes duplicate rows from all result sets.

DISTINCT

Drops duplicate rows from all result sets prior to the INTERSECT comparison. Columns containing a NULL value are considered duplicates. (If neither ALL nor DISTINCT is used, the DISTINCT behavior is the default.)

CORRESPONDING

Specifies that only columns with the same name in both queries are returned, even if both queries use the asterisk shortcut.

BY

Specifies that only the named columns are returned, even if more columns with corresponding ...