|
Computes the intersection between the result sets of two or more queries.
[ 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-hint :
MATERIALIZED VIEW OPTIMIZATION option-value
| FORCE OPTIMIZATION
| option-name = option-value
option-name : identifier
option-value : hostvar (indicator allowed), string, identifier, or number
NoteYou cannot use the FOR, FOR XML, WITH, or OPTION clause in the query-block. |
OPTION clause This clause provides hints as to how to process the query. The following query hints are supported:
MATERIALIZED VIEW OPTIMIZATION 'option-value' Use the MATERIALIZED VIEW OPTIMIZATION clause to specify how the optimizer should make use of materialized views when processing the query. The specified option-value overrides the materialized_view_optimization database option for this query only. Possible values for option-value are the same values available for the materialized_view_optimization database option. See materialized_view_optimization option [database].
FORCE OPTIMIZATION When a query specification contains only simple queries (single-block, single-table queries that contain equality conditions in the WHERE clause that uniquely identify a specific row), it typically bypasses cost-based optimization during processing. In some cases you may want cost-based optimization to occur. For example, if you want materialized views to be considered during query processing, view matching must occur. However, view matching only occurs during cost-base optimization. If you want cost-based optimization to occur for a query, but your query specification contains only simple queries, specify the FORCE OPTIMIZATION option to ensure that the optimizer performs cost-based optimization on the query.
Similarly, specifying the FORCE OPTIMIZATION option in a SELECT statement inside of a procedure forces the use of the optimizer for any call to the procedure. In this case, plans for the statement are not cached. For more information on simple queries and view matching, see Phases of query processing, and Improving performance with materialized views.option-name = option-value Specify an option setting that takes precedence over any public or temporary option settings that are in effect, for this statement only. The supported options are:
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.
Must have SELECT permission for each query-block.
None.
SQL/2003 Feature F302.
For examples of INTERSECT usage, see Set operators and NULL.