INTERSECT statement

Computes the intersection between the result sets of two or more queries.

Syntax

[ WITH temporary-views ] query-block
  INTERSECT [ ALL | DISTINCT ] query-block
[ ORDER BY [ integer | select-list-expression-name ] [ ASC | DESC ], ... ]
[ FOR XML xml-mode ]
[ OPTION( query-hint, ... ) ]

query-block : a query block

query-hint :
MATERIALIZED VIEW OPTIMIZATION option-value
| FORCE OPTIMIZATION
| option-name = option-value

option-name : identifier

option-value : 
hostvar (indicator allowed)
| string
| identifier
| number

Parameters

query-block
Query blocks are described in the documentation on common elements in SQL syntax.
FOR XML clause
The FOR XML clause is documented in the SELECT statement.
OPTION clause
Use this clause to specify hints for executing the statement. The following hints are supported:
- MATERIALIZED VIEW OPTIMIZATION option-value
- FORCE OPTIMIZATION
- option-name = option-value. A OPTION( isolation_level = ... ) specification in the query text overrides all other means of specifying isolation level for a query.

Remarks

INTERSECT computes the set intersection between the result sets of two query blocks. Query blocks can be nested, and can in turn be comprised of nested SELECT statements or the set operators UNION, EXCEPT, or INTERSECT. Specifying INTERSECT alone is equivalent to specifying INTERSECT DISTINCT.

INTERSECT ALL implements bag intersection rather than set intersection. For example, if the first query-block contains 5 (duplicate) rows with specific values, and the second query-block contains 3 duplicate rows with identical values to the first, then INTERSECT ALL will return 3 rows.

The results of INTERSECT are the same as INTERSECT ALL if either query-block does not contain duplicate rows.

The two query-block result sets must be UNION-compatible; they must each have the same number of items in their respective SELECT lists, and the types of each expression should be comparable. If corresponding items in two SELECT lists have different data types, the database server chooses a data type for the corresponding column in the result and automatically convert the columns in each query-block appropriately.

The column names displayed are the same column names that are displayed for the first query-block and these names are used to determine the expression names to be matched with the ORDER BY clause. An alternative way of customizing result set column names is to use a common table expression (the WITH clause).

Privileges

You must have the SELECT ANY TABLE system privilege, or be the owner of the objects specified in query-block, or have SELECT privileges on each query block.

Side effects

None.

Standards

ANSI/ISO SQL Standard
INTERSECT is optional ANSI/ISO SQL Language Feature F302. Explicitly specifying the DISTINCT keyword with INTERSECT is optional ANSI/ISO SQL Language Feature T551. Specifying an ORDER BY clause with INTERSECT is SQL Language Feature F850. A query-block that contains an ORDER BY clause constitutes ANSI/ISO SQL Feature F851. A query block that contains a row-limit clause (SELECT TOP or LIMIT) comprises optional ANSI/ISO SQL Language Feature F857 or F858, depending on the context. The FOR XML and OPTION clauses are not part of the Standard.
Transact-SQL
INTERSECT is not supported by Adaptive Server Enterprise. However, both INTERSECT ALL and INTERSECT DISTINCT can be used in the Transact-SQL dialect supported by SQL Anywhere.