Oracle's MINUS and SQL Server's EXCEPT

Today I was a little surprised to find a difference between Oracle and SQL Server syntax that I wasn't aware of. I had a need to find a set of rows which had been incorrectly geographically coded. The incorrect coding took a number of forms and I wanted to make sure that I wasn't double counting.

Easy - I done the same type of thing many times before with Oracle. Just use the MINUS operator. Perhaps not. SQL Server complained about the MINUS keyword. With a little digging a found that the SQL Server equivalent is EXCEPT.

It does the same thing. It will return any rows from the first query where are not also returned by the second query. The syntax is:

SELECT col_a, col_b, col_c
FROM view1
MINUS
SELECT col_a, col_b, col_c
FROM view2

There are a few gotchas to be aware of. The number and order of the columns in the queries used must be the same, and the data types must also be the same.

I was even more surprised to find that the EXCEPT keyword actually is the ANSI SQL standard. Oracle's MINUS keyword is actually the odd one out!