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.
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 |
There is technically no limit to the number of queries that you may combine with the INTERSECT set operator. The general syntax is:
<SELECTstatement1
> INTERSECT [ALL | DISTINCT] [CORRESPONDING [BY (column1
,column2
, ...)] ] <SELECTstatement2
> INTERSECT [ALL | DISTINCT] [CORRESPONDING [BY (column1
,column2
, ...)] ] ...
Includes duplicate rows from all result sets.
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.)
Specifies that only columns with the same name in both queries are returned, even though both queries use the asterisk shortcut.
Specifies that only the named columns are returned, even if more columns ...
No credit card required