Recently while trying to get in some extra practice using set operators (EXCEPT, INTERSECT & UNION) and querying data with Toad for Oracle; I came to a shocking realization. There is no EXCEPT expression in Oracle!

If you are not familiar, the EXCEPT operator in SQL basically compares the results between two data sets and returns the differences, depending on which table you reference first. For example if you have the following simple statement:

Records would be returned for TableA where they didn’t exist in TableB. This is sort of like performing a JOIN and specifying where ColumnA in TableB is NULL. A handy replacement I was able to find for this in Oracle was the MINUS operator. The syntax is basically the same:

Before EXCEPT and INTERSECT you pretty much had to rely on JOIN and EXISTS to manipulate data sets in this manner. Some developers fancy using SET operators while others would just prefer to use JOINS.

My belief is that it comes down to what you are accustomed to doing on a daily basis. However, if you want to pass the Microsoft exam 70-461 you should become familiar with them in detail.

Like this post? Share it!