ASA SQL Reference
SQL Statements
Computes the intersection between the result sets of two or more queries.
select-statement
INTERSECT [ ALL | DISTINCT ] select-statement
[ INTERSECT [ ALL | DISTINCT ] select-statement ] ...
[ ORDER BY integer [ ASC | DESC ], ... ]
The intersection between the result sets of several SELECT statements can be obtained as a single result using INTERSECT or INTERSECT ALL. INTERSECT DISTINCT is identical to INTERSECT.
The component SELECT statements must each have the same number of items in the select list, and cannot contain an ORDER BY clause.
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, Adaptive Server Anywhere chooses a data type for the corresponding column in the result and automatically convert the columns in each component SELECT statement appropriately. If ORDER BY is used, only integers are allowed in the order by list. These integers specify the position of the columns to be sorted.
The column names displayed are the same column names that are displayed for the first SELECT statement. An alternative way of customizing result set column names is to use the WITH clause on the SELECT statement.
Must have SELECT permission for each of the component SELECT statements.
None.
SQL/92 Entry-level.
SQL/99 Feature F302.
Sybase Supported by Adaptive Server Enterprise.
For examples of INTERSECT usage, see Set operators and NULL.