|
Computes the difference between the result sets of two or more queries.
[ WITH temporary-views ] query-block
EXCEPT [ 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 differences between the result sets of several query blocks can be obtained as a single result using EXCEPT or EXCEPT ALL. EXCEPT DISTINCT is identical to EXCEPT.
The query-block must each have the same number of items in the select list.
The number of rows in the result set of EXCEPT ALL is exactly the difference between the number of rows in the result sets of the separate queries.
The results of EXCEPT are the same as EXCEPT ALL, except that when using EXCEPT, duplicate rows are eliminated before the difference 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 specification 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 EXCEPT DISTINCT is a core feature. EXCEPT ALL is feature F304.
For examples of EXCEPT usage, see Set operators and NULL.