Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

Collection Contents DROP VARIABLE statement Next PDF iAnywhere.com - EXCEPT statement

Collection Contents DROP VARIABLE statement Next PDF iAnywhere.com - EXCEPT statement

Collection Contents DROP VARIABLE statement Next PDF iAnywhere.com - EXCEPT statement

Collection Contents DROP VARIABLE statement Next PDF iAnywhere.com - EXCEPT statement

Collection Contents DROP VARIABLE statement Next PDF iAnywhere.com - EXCEPT statement

Collection Contents DROP VARIABLE statement Next PDF iAnywhere.com - EXCEPT statement

Collection Contents DROP VARIABLE statement Next PDF iAnywhere.com - EXCEPT statement

Collection Contents DROP VARIABLE statement Next PDF

SQL Anywhere® Server - SQL Reference
  SQL Statements
EXCEPT statement


Computes the difference between the result sets of two or more queries.

Syntax

[ 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

Parameters
Note

You 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:

Remarks

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.

Permissions

Must have SELECT permission for each query-block.

Side effects

None

See also
Standards and compatibility
Example

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


Collection Contents DROP VARIABLE statement Next PDF