EXCEPT clause

Returns rows that exist in the result set for the query block preceding EXCEPT that do not exist in the result set of the query block following EXCEPT.

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
Parameters

  • main-query-block   A query block against which one or more query blocks is compared.

  • except-query-block   A query block to the right of an EXCEPT clause. The result of an except-query-block is compared with the result of main-query-block to identify rows that exist only in main-query-block.

  • EXCEPT clause   If there are duplicate rows in main-query-block that do not match rows in the except-query-block(s), the duplicates appear in the result if EXCEPT ALL is specified. To suppress duplicates in the result, specify EXCEPT or EXCEPT DISTINCT instead.

  • OPTION clause   Use this clause to specify hints for executing the statement. The following hints are supported:

    • MATERIALIZED VIEW OPTIMIZATION option-value
    • FORCE OPTIMIZATION
    • option-name = option-value

    For a description of these options, see the OPTIONS clause of the SELECT statement.

Remarks

Use the EXCEPT clause to compare the results of main-query-block to one or more except-query-blocks, and return only those rows that exist in main-query-block. Specify EXCEPT or EXCEPT DISTINCT if you do not want duplicates in main-query-block to appear as duplicates in the results. Otherwise, specify EXCEPT ALL.

EXCEPT is identical to EXCEPT DISTINCT.

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

  • SQL/2003   EXCEPT DISTINCT is a core feature. EXCEPT ALL is feature F304.

Example

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

Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1