Post reply

DISTINCT on multiple tables

  • September 28, 2009 at 1:41 pm

    #135444

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

  • 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

  • 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

  • September 28, 2009 at 1:52 pm

    #1059191

    I'm curious, why the nested distinct clauses?

  • 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.

  • 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

  • September 29, 2009 at 7:16 am

    #1059444

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply