SQL in a Nutshell, 2nd Edition

Name

INTERSECT Set Operator

Synopsis

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.

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 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 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 are used, DISTINCT behavior is the default.)

CORRESPONDING

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

BY

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