SQL Except statement with Order By at 9:05 PM

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.