none
Alternative to "union" function in sql server 2008

    Question

  • Hi,

    I have two table emp1 and emp2

    emp1 table data 1 sql2000, 2 sql2008, 5 sql2008R2

    emp2 table data 1 sql2000,3 sql2005,4 sql2012,5 sql2008R2

    SELECT * FROM dbo.emp1

    UNION

    SELECT * FROM dbo.emp2

    when I use Union function, I am getting the without duplicate records, but customer asked don't use "UNION" function so how to create/achieve the code.

    thanks for advance

    Ram

    Monday, July 01, 2013 12:51 PM

Answers

  • Try:

    SELECT COALESCE(dbo.emp1.ID, dbo.emp2.ID) AS ID, 
    COALESCE(dbo.emp1.Value, dbo.emp2.Value)AS Value
    FROM dbo.emp1
    FULL OUTER JOIN dbo.emp2
    ON dbo.emp2.ID = dbo.emp1.ID

    This should return records from emp1 regardless of of whether there is a match in emp2, and also the other way around.  There will be NULLs where a field doesn't exist, and you can handle these NULLs with COALESCE or a CASE statement.  Full Outer Joins are rare, but this is a case where you can use one.

    • Edited by John Smith 3 Monday, July 01, 2013 5:50 PM blah
    • Marked as answer by BIRam Tuesday, July 02, 2013 9:55 AM
    Monday, July 01, 2013 5:49 PM

All replies

  • http://msdn.microsoft.com/en-us/library/ms188055.aspx

    SELECT * FROM dbo.emp1

    INTERSECT

    SELECT * FROM dbo.emp2


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Monday, July 01, 2013 12:59 PM
  • thanks for your reply,

    if we used "INTERSECT" it is showing matching records. I want to all the records without duplicate records.

    Monday, July 01, 2013 1:44 PM
  • Refer the below solution,

    declare @emp1 table(id int,version nvarchar(20))
    insert into @emp1   values(1, 'sql2000')
    insert into @emp1   values(2, 'sql2008')
    insert into @emp1   values(5, 'sql2008R2')
    ----------------------
    declare @emp2 table(id int,version nvarchar(20))
    insert into @emp2 values(1, 'sql2000')
    insert into @emp2 values(3, 'sql2005')
    insert into @emp2 values(4, 'sql2012')
    insert into @emp2 values(5, 'sql2008R2')
    ------------------ using UNION
    select * from @emp1
    union
    select * from @emp2
    ------------------ alternate solution
    declare @emp3 table(id int,version nvarchar(20))
    insert into @emp3 select * from @emp2
    insert into @emp3 select * from @emp1
    select distinct * from @emp3


    Regards, RSingh

    Monday, July 01, 2013 3:09 PM
  • How will intersect help here?  Intersect will only show those IDs that exist in both tables.

    Monday, July 01, 2013 3:41 PM
  • Try:

    SELECT COALESCE(dbo.emp1.ID, dbo.emp2.ID) AS ID, 
    COALESCE(dbo.emp1.Value, dbo.emp2.Value)AS Value
    FROM dbo.emp1
    FULL OUTER JOIN dbo.emp2
    ON dbo.emp2.ID = dbo.emp1.ID

    This should return records from emp1 regardless of of whether there is a match in emp2, and also the other way around.  There will be NULLs where a field doesn't exist, and you can handle these NULLs with COALESCE or a CASE statement.  Full Outer Joins are rare, but this is a case where you can use one.

    • Edited by John Smith 3 Monday, July 01, 2013 5:50 PM blah
    • Marked as answer by BIRam Tuesday, July 02, 2013 9:55 AM
    Monday, July 01, 2013 5:49 PM
  • Thank you it is work perfect.
    Tuesday, July 02, 2013 10:00 AM