Returns the set difference of two query blocks.
Syntax[ WITH temporary-views ] main-query-block EXCEPT [ ALL | DISTINCT ] except-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
main-query-block : A query block. See Common elements in SQL syntax.
except-query-block : A query block. See Common elements in SQL syntax.
option-name : identifier
option-value : hostvar (indicator allowed), string, identifier, or number
Parametersmain-query-block A query block, comprising a SELECT statement or a query expression (possibly nested).
except-query-block A query block, comprising a SELECT statement or a query expression (possibly nested).
FOR XML clause For a description of the FOR XML clause, see SELECT statement.
OPTION clause Use this clause to specify hints for executing the statement. The following hints are supported:
OPTION( isolation_level = ... ) specification in the query text overrides all other means of specifying isolation level for a query.
For a description of these options, see OPTION clause, SELECT statement.
RemarksThe EXCEPT statement returns all rows in main-query-block except those that also appear in the except-query-block. Specify EXCEPT or EXCEPT DISTINCT if you do not want duplicates from main-query-block to appear as duplicates in the result. Otherwise, specify EXCEPT ALL. Note that query blocks can be nested.
The use of EXCEPT alone is equivalent to EXCEPT DISTINCT.
The main-query-block and the except-query-block 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, SQL Anywhere chooses a data type for the corresponding column in the result and automatically convert the columns in each query-block appropriately.
EXCEPT ALL implements bag difference rather than set difference. For example, if main-query-block contains 5 (duplicate) rows with specific values, and except-query-block contains 2 duplicate rows with identical values, then EXCEPT ALL will return 3 rows.
The results of EXCEPT are the same as the results of EXCEPT ALL if main-query-block does not contain duplicate rows.
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).
PermissionsMust have SELECT permission for each query-block.
Side effectsNone
See also Standards and compatibilitySQL/2008 EXCEPT DISTINCT is a core feature of the SQL/2008 standard; EXCEPT ALL comprises the optional SQL language feature F304. Explicitly specifying the DISTINCT keyword with EXCEPT is optional SQL language feature T551 of the SQL/2008 standard. Specifying an ORDER BY clause with EXCEPT is SQL language feature F850. A query-block that contains an ORDER BY clause constitutes SQL/2008 feature F851. A query block that contains a row-limit clause (SELECT TOP or LIMIT) comprises optional SQL language feature F857 or F858, depending on the context. The FOR XML clause and the OPTION clause are vendor extensions.
Transact-SQL EXCEPT is not supported by Adaptive Server Enterprise. However, both EXCEPT ALL and EXCEPT DISTINCT can be used in the Transact-SQL dialect supported by SQL Anywhere.
ExampleFor examples of EXCEPT usage, see Set operators and NULL.
 |
Discuss this page in DocCommentXchange.
|
Copyright © 2010, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.0 |