SQL Except statement with Order By

The Except statement is easy to understand. For example, the result of "result set A Except result set B" is like below in grey:
But thing will easily go wrong in SQL statement when you use Except and Order By.

Take AdventureWorks database as an example here:

1. Run the 1st statement and notice BusinessEntityID=66 is in the result:

2. Run the 2nd statement and notice BusinessEntityID=66 is also in the result:


3. How about combining those two SQL statements together with Except? From the concept, the record BusinessEntityID=66 should not be in the result, right? Let's take a look:

It is still there! Why?!

The reason is: Order By actually belongs to the first Select statement, not the 2nd.

This is the result set for the 2nd statement without Order By:


The record BusinessEntityID=66 is NOT in the result. So the result of Except statement is actually correct.

1 comments:

Galia Co Hagan said...

Hi, Great.. Tutorial is just awesome..It is really helpful for a newbie like me.. I am a regular follower of your blog. Really very informative post you shared here. Kindly keep blogging. If anyone wants to become a .Net developer learn from Dot Net Training in Chennai. or learn thru Dot Net Training in Chennai. Nowadays Dot Net has tons of job opportunities on various vertical industry.
or Javascript Training in Chennai. Nowadays JavaScript has tons of job opportunities on various vertical industry.