EXCEPT (SQL)
Synopsis
query1 EXCEPT query2
Description
An EXCEPT combines two or more queries into a single query and returns data that is found in the result set of the first query, but not of the second query. The result set can be thought of as the difference between the first query and the second query.
For an EXCEPT to be possible between two queries, the number of columns specified by each must match. Specifying queries that return different numbers of columns results in a SQLCODE -9 error. You can specify a NULL column in one query to pair with a data column in another query to match the number of columns.
The result column types are determined by the types of the columns selected by the first query. Comparisons across types are made by converting the types of the columns in the second query to the types of the columns in the first query.
Arguments
query
A query, usually a SELECT statement, that can combine one or more SELECT statements.
Examples
The following example creates a result set that contains a row for every Name found in the first query, but not in the second query. The ORDER BY clause operates on the result and orders the result set by the Name:
SELECT Name,Address,NULL
FROM Sample.Employee
EXCEPT
SELECT Name,Address,Age
FROM Sample.Person
ORDER BY Name
