Friday, March 23, 2018

Alternative To Union In T-SQL

Good morning!
In the event that you need to replace your queries using Union such as below:
Use DBJersonsHW
go
SELECT         
 DISTINCT [EmpName] AS [EmployeeName],
        EmpAddress As [Address]  
FROM tblEmployeesMain 
UNION 
SELECT 
 DISTINCT [EmpName] AS [EmployeeName],
 EmpAddress As [Address] 
FROM tblEmployeesMetro 
ORDER BY [EmpName];
The alternative for that is to use Full Outer Join as presented below:
Use DBJersonsHW
go
SELECT 
 Distinct Coalesce(tblEmployeesMain.EmpName, tblEmployeesMetro.EmpName) AS EmployeeName,
 Coalesce(tblEmployeesMain.EmpAddress, tblEmployeesMetro.Address) AS Address,
FROM tblEmployeesMain   
 FULL Outer JOIN tblEmployeesMetro 
on tblEmployeesMain.EmpName = tblEmployeesMetro.EmpName 
order by EmpName 

Cheers! :-)

0 comments:

Post a Comment