Description 

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

Syntax 

select-statement
  EXCEPT [ ALL | DISTINCT ] select-statement
[ EXCEPT [ ALL | DISTINCT ] select-statement ] ...
[ ORDER BY integer [ ASC | DESC ], ... ]

Usage 

The differences between the result sets of several SELECT statements can be obtained as a single result using EXCEPT or EXCEPT ALL. EXCEPT DISTINCT is identical to EXCEPT.

The component SELECT statements must each have the same number of items in the select list, and cannot contain an ORDER BY clause.

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, Adaptive Server Anywhere will choose a data type for the corresponding column in the result and automatically convert the columns in each component SELECT statement appropriately. If ORDER BY is used, only integers are allowed in the order by list. These integers specify the position of the columns to be sorted.

The column names displayed are the same column names that are displayed for the first SELECT statement. An alternative way of customizing result set column names is to use the WITH clause on the SELECT statement.

Permissions 

Must have SELECT permission for each of the component SELECT statements.

Side effects 

None

See also 

Standards and compatibility 

• SQL/92    Entry-level.

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

• Sybase    Supported by Adaptive Server Enterprise.

Example