Except Operator

Except operator compares the result sets from two separate queries and returns the resultant subset. The final result set returns all the rows from the table mentioned on the left side of the except operator avoiding any matching rows pertaining to the table specified on the right side of the except operator. Like Union Operator, it also have some rules to follow like:

• If two result sets are being combined using union operator then both the result sets must contain same number of columns.
• The data type of the corresponding columns in both the result sets must be same. If not then it must be converted so that both have the same type.
• The final result set use the column names from the first result set.
• The order by clause is used at the end of the last select statement.
• The order by clause uses the column name(s) from the first select statement.
• Each select statement should include its own filtering criterion.

e.g.

select customerid as c from AdventureWorks.sales.customer

except

select customerid as cc from AdventureWorks.sales.salesorderheader order by c