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