INTERSECT clause

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 : see Common elements in SQL syntax

query-hint :
MATERIALIZED VIEW OPTIMIZATION option-value
| FORCE OPTIMIZATION
| option-name = option-value
option-name : identifier
option-value : hostvar (indicator allowed), string, identifier, or number
Parameters
  • 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

    For a description of these options, see the OPTIONS clause of the SELECT statement.

Remarks

The intersection between the result sets of several query blocks can be obtained as a single result using INTERSECT or INTERSECT ALL. INTERSECT DISTINCT is identical to INTERSECT.

The query blocks must each have the same number of items in the select list.

The results of INTERSECT are the same as INTERSECT ALL, except that when using INTERSECT, duplicate rows are eliminated before the intersection between the result sets is computed.

If corresponding items in two select lists have different data types, SQL Anywhere chooses a data type for the corresponding column in the result and automatically convert the columns in each query-block appropriately. The first query-block of the UNION is used to determine the names to be matched with the ORDER BY clause.

The column names displayed are the same column names that are displayed for the first query-block. An alternative way of customizing result set column names is to use the WITH clause on the query-block.

Permissions

Must have SELECT permission for each query-block.

Side effects

None.

See also
Standards and compatibility
  • SQL/2003   Feature F302.

Example

For examples of INTERSECT usage, see Set operators and NULL.