Simulate Full Outer Join

You can simulate full outer join without using FULL keyword by using below query.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
CREATE TABLE #temp1
(
ID INT,
FirstName VARCHAR(50)
)
  
 
INSERT INTO #temp1
VALUES(1,'RAM');
INSERT INTO #temp1
VALUES(2,'Ravi');
INSERT INTO #temp1
VALUES(3,'Rahul');
CREATE TABLE #temp2
(
ID INT,
LastName VARCHAR(50)
)
INSERT INTO #temp2
VALUES(1,'Sanghvi');
INSERT INTO #temp2
VALUES(3,'Jain');
INSERT INTO #temp2
VALUES(4,'Agrawal');
SELECT DISTINCT ID1,FirstName,ID2,LastName
FROM (SELECT T1.ID AS ID1,FirstName, T2.ID AS ID2, LastName FROM #temp1 AS T1
LEFT JOIN #temp2 AS T2
on  T1.ID = T2.ID
UNION ALL
SELECT
T1.ID AS ID1,FirstName, T2.ID AS ID2, LastName FROM #temp1 AS T1
RIGHT  JOIN #temp2 AS T2
on T1.ID = T2.ID) AS A