Reputation: 1
We have two tables table1:[1,1,null,0,0] and table2: [1,null,0,2]. And i wand to do Full Outer Join. In MySQL, since there's no direct support for FULL OUTER JOIN, so we are simulating it using LEFT JOIN, RIGHT JOIN, and UNION or UNION ALL.
table1 table2
col col
1 1
1 null
null 0
0 2
0
SO, the output of Inner join will be as follow:
col col
1 1
1 1
0 0
0 0
total output of inner join = 4
SO, the output of LEFT JOIN will be as follow:
col col
1 1
1 1
null null
0 0
0 0
total output of Left join = 4 (no. of rows of inner join) + 1 (1 non matching item of left table) = 5
Now, the output of RIGHT JOIN will be as follow:
col col
1 1
1 1
null null
0 0
0 0
null 2
total output of Right join = 4 (no. of rows of inner join) + 2 (2 non matching item of left table) = 6
NOW, the output of FULL OUTER JOIN should be as follow:
col col
1 1
1 1
0 0
0 0
null null
null null
null 2
total output of inner join = 4 (no. of rows of inner join) + 1 (1 non matching item of left table) + 2 (2 non matching item of left table) = 7
But we run the code following code in MySQL
select t1.col as col1, t2.col as col2
from table1 t1
left join table2 t2
on t1.col = t2.col
union all
select t1.col as col1, t2.col as col2
from table1 t1
right join table2 t2
on t1.col = t2.col
The Output generated is as following
col col
1 1
1 1
null null
0 0
0 0
1 1
1 1
null null
0 0
0 0
null 2
Thus there are 11 rows, which means the output of inner join is repeated, So we do union in place of union all then we get only 4 rows as follow
col col
1 1
null null
0 0
null 2
But the expected output is as follow:
col col
1 1
1 1
null null
0 0
0 0
null null
null 2
select t1.col as col1, t2.col as col2
from table1 t1
left join table2 t2
on t1.col = t2.col
union all
select t1.col as col1, t2.col as col2
from table1 t1
right join table2 t2
on t1.col = t2.col
Upvotes: 0
Views: 25