Search This Blog

Alternate of Union in SQL

Alternate of Union in SQL 

Alternate of Union –How to get Same Result as union without using Union in SQL

Issue-One of my colleague asked me -can we achieve the union result without using union or is there any alternative way to get same result as union without using Union.

Issue Description-He told me that he went for an interview in some ‘XYZ’ company there he was asked with this question that How to get result same as what union does without using SQL.

Issue Solutions: So there are two ways which i know to do this but  there can be other ways also add in comments if you know more.

Below are two test table structures

create table Emp (id int, Name nvarchar(100), EmpAddress nvarchar(100))
Insert into Emp values (1,'A','AP'),(2,'B','MP'),(3,'C','JK'),(4,'D','TN')

--Second Table
create table Emp1(id int, Name nvarchar(100),EmpAddress nvarchar(100))

Insert into Emp1 values (5,'E','Kar'),(6,'F','Kerl'),(7,'G','UP'),(8,'H','Delhi'),(9,'I','Haryana'),(10 ,'J','UK')

Output Required. Same as union but without using Union
select * from Emp
union--without using Union
select * from Emp1


Method 1-Create a temp table and insert both table Record into temp table as below

--Select * into to create a new temp table employee and inserting first table record ---into it
select *   into #employee from Emp

--once first table record gets inserted into temp table insert second table records
insert into #employee
select * from Emp1

--Output verification:
Select * from #employee

Method 2-By using a full outer joins and coalesce like below:

with cte as
 (
select a.id as AID,a.name AName,a.EmpAddress AEmpAddress ,b.* from emp a full outer join emp1 b on 1=2
)
--as we have given condition 1=2 so it will show all records from both the table
--Now we will use coalesce to show value instead of null in first table record
Select coalesce(aid,id)ID,coalesce(aname,name)Name,coalesce(aEmpaddress,EmpAddress)EmpAddress
from cte

So I have showed two ways to achieve the result without using Union if you know more write in comments.







.

1 comment:

  1. Without CTE, we can do it as follows.

    select coalesce(e.id,e1.id) as ID,
    coalesce(e.Name,e1.Name) as Name,
    coalesce(e.EmpAddress,e1.EmpAddress) as EmpAddreess from Empp e full join empp1 e1 on e.id=e1.id

    ReplyDelete