Nice question
codebyo (10/10/2011)
That looks and executes right... but I can't understand why the result is not only the number 4.After the EXCEPT, only the number 4 remains. So where does that 5 come from in the INTERSECT part?
Please be aware that the INTERSECT operand has higher precedence than the EXCEPT and UNION operands (http://msdn.microsoft.com/en-us/library/ms188055.aspx):
EXCEPT and INTERSECT (Transact-SQL)
Remarks
If EXCEPT or INTERSECT is used together with other operators in an expression, it is evaluated in the context of the following precedence:
1. Expressions in parentheses
2. The INTERSECT operand
3. EXCEPT and UNION evaluated from left to right based on their position in the expression
So, the code given in the QOTD is equal to the following pseudocode:
( (1, 2, 5)
union
(1, 2, 4) )
except
( (1, 2, 5)
intersect
(1, 2, 4) )
Which is equal to the following pseudocode:
(1, 2, 4, 5)
except
(1, 2)
Which gives us "4, 5" as a result.