SQL-Union ALL and Except

Question

I am seeing a strange behavior when I do except and union statements in SQL.

I have two tables

Select * from #old

Data looks like this

oid1    oid2    co
   1      11     1
   2      22     1
   3      33     1
   4      55     1

Select * from #new

nid1    nid2    co
   1      11     3
   2      22     1
   3      33     1
   4      44     1
   4      55     1

This is my final query

Select * from #old
    except
    Select * from #new
    union all
    Select * from #new
    except
    Select * from #old

and gives these records

oid1    oid2    co
   1      11     3
   4      44     1

Question I have is.. Shouldn't there be another row in this from the first except clause:

Select * from #old
except
Select * from #new

which is

oid1    oid2    co    
   1      11     1

Shouldn't the final query have 3 rows instead of only 2, since not all columns are the same.

Answer 1

You seem to think that the query is interpreted as:

(Select * from #old
 except
 Select * from #new
)
union all
(Select * from #new
 except
 Select * from #old
)

But no. It is interpreted as:

((Select * from #old
  except
  Select * from #new
 )
 union all
 Select * from #new
)
except
Select * from #old

This is equivalent to:

Select * from #new
except
Select * from #old

which is what your query returns.

This is explained in the documentation:

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 operator

3. EXCEPT and UNION evaluated from left to right based on their position in the expression