O'Reilly logo

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

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

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