RAJU SINGH
RAJU SINGH

Reputation: 1

MySQL Full Outer Join having duplicated rows

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

Answers (0)

Related Questions