CODEWITHSUNDEEP
sql-serverunionexcept
Peter
Peter

Reputation: 23

Sql Server Union All dont get all rows

Simple question about SQL Server.

I expected that this query:

-- part 1    
select * from table01
except
select * from table02
--
union all
-- part 2
select * from table02
except
select * from table01

would give me the same result set as if doing the two part 1 and part 2 separately. But I just get two rows from the query but two rows from both of the parts. Would be interesting to know why this is happening?

Upvotes: 2

Views: 90

Answers (2)

KumarHarsh
KumarHarsh

Reputation: 5094

Declare @t table(col1 int)
insert into @t values(1),(2)

Declare @t1 table(col1 int)
insert into @t1 values(1),(3)

1.)  select * from @t
      except
2.)  select * from @t1
       union all
3)    select * from @t1
      except
4)    select * from @t


1 ) and 2 ) give 2
then previos result aand 3 gives 2,1,3
then again last result and 4) give 3
so final Result=3

Upvotes: 0

usr
usr

Reputation: 171178

except contains an implicit distinct for both inputs. Probably, you did not expect that. Without concrete example data it is hard to tell what exactly happened.

SELECT name FROM sys.objects
EXCEPT
SELECT name FROM sys.objects WHERE 0=1

Is equivalent to

SELECT DISTINCT name FROM sys.objects

Both have the same execution plan (including a distinct).

Upvotes: 3

Related Questions