Saturday, February 7, 2015

Full Outer Join in MySQL

In this case, I have 2 tables that need to join and show data in both tables even there is no data in one of the tables. For example, I have first table with data:

table1
id|male
1|Richard
2|Andy
3|Bagyo

and the other table

table2
id|female
2|Jane
3|Sulastri
4|Linda

I want to show the data like this:

id|male|id|female
1|Richard|-|-
2|Andy|2|Jane
3|Bagyo|3|Sulastri
-|-|4|Linda

By default, currently MySQL don’t support FULL OUTER JOIN. To achieve this, you can combine LEFT JOIN and RIGHT JOIN.

SELECT *
FROM `table1`
LEFT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`

UNION

SELECT *
FROM `t1`
RIGHT OUTER JOIN `t2` ON `t1`.`id` = `t2`.`id`;

We can think of a UNION as meaning "run both of these queries, then stack the results on top of each other", with some rows will come from the first query and some from the second. UNION in MySQL will eliminate exact duplicates, so the result of the UNION only lists the same data once.

0 comments:

Post a Comment