Not getting expected output in following sql server 2008 query

Question

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?

podiluska · Accepted Answer · 2013-08-01 07:41:02Z

5

In stages

select 2 except select 0

gives

Then

union all select 0

gives

2
0

Then

except select 2

removes the 2 giving

If you want 2 and 0, use

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

answered Aug 1, 2013 at 7:41

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gidil · Accepted Answer · 2013-08-01 07:43:20Z

0

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)

answered Aug 1, 2013 at 7:43

Gidil

4,1472 gold badges38 silver badges50 bronze badges

Collectives™ on Stack Overflow

Not getting expected output in following sql server 2008 query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related