DISTINCT on multiple tables

• Tara-1044200

  SSCoach

  Points: 15951

  More actions

  September 28, 2009 at 1:41 pm

  #135444

  How can i get distinct empid's from 3 tables Emp1,Emp2,Emp3.

• Adam Angelini

  SSCrazy Eights

  Points: 8548

  More actions

  September 28, 2009 at 1:50 pm

  #1059189

  select distinct empID from

  (select empid from emp1

  union select empid from emp2

  union select empid from emp3) as emp

• Bradley Jacques

  Ten Centuries

  Points: 1301

  More actions

  September 28, 2009 at 1:50 pm

  #1059190

  I would love to help you; can you post the table create statements.

  I assume a solution would be:

  Select Distinct EmpID from Emp1

  UNION

  Select Distinct EmpID from Emp2

  UNION

  Select Distinct EmpID from Emp3

• Bradley Jacques

  Ten Centuries

  Points: 1301

  More actions

  September 28, 2009 at 1:52 pm

  #1059191

  I'm curious, why the nested distinct clauses?

• Adam Angelini

  SSCrazy Eights

  Points: 8548

  More actions

  September 28, 2009 at 1:58 pm

  #1059193

  Just a matter of preference and performance. The 2 queries will produce the same results, but different query plans. I would suggest testing both to see what one performs better.

• ArkWare

  Mr or Mrs. 500

  Points: 525

  More actions

  September 29, 2009 at 6:51 am

  #1059428

  I'm wondering why the use of Distinct at all...

  BOL: By default, the UNION operator removes duplicate rows from the result set. If you use ALL, all rows are included in the results and duplicates are not removed.

  DECLARE @emp1 table (ID int)

  DECLARE @emp2 table (ID int)

  DECLARE @emp3 table (ID int)

  insert into @emp1

  select 1

  union all

  select 2

  union all

  select 2

  union all

  select 3

  insert into @emp2

  select 1

  union all

  select 2

  union all

  select 2

  union all

  select 3

  insert into @emp2

  select 1

  union all

  select 2

  union all

  select 2

  union all

  select 3

  -- results

  SELECT ID from @emp1

  UNION

  SELECT ID from @emp2

  UNION

  SELECT ID from @emp3

• Bradley Jacques

  Ten Centuries

  Points: 1301

  More actions

  September 29, 2009 at 7:16 am

  #1059444

  You are 100% right... however, when in rome?