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

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

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