CODEWITHSUNDEEP
sqlsql-server
conetfun
conetfun

Reputation: 1615

Not getting expected output in following sql server 2008 query

What should be the output of the following two queries in sql server 2008?

select 2
except
select 0
union all
select 0
except
select 2


select 2
union all
select 0

The first query is giving output as 0 but I am expecting union of 2 and 0, while the second query is displaying expected result of 0 and 2 in result set.

Why is it so?

Upvotes: 1

Views: 84

Answers (2)

Gidil
Gidil

Reputation: 4137

It's just like math, the order is important and so are brackets.
Try this:

(SELECT 2 
 EXCEPT 
 SELECT 0) 
UNION ALL 
(SELECT 0 
 EXCEPT 
 SELECT 2)

Upvotes: 0

podiluska
podiluska

Reputation: 51494

In stages

select 2 except select 0

gives

2

Then

union all select 0

gives

2
0

Then

except select 2

removes the 2 giving 

0

If you want 2 and 0, use

(select 2 except select 0) 
union all 
(select 0 except select 2)

Upvotes: 5

Related Questions