MySQL – FULL OUTER JOIN

[adsense id=”0514458240″ width=”468″ height=”60″]

Unfortunately, MySQL 5.x does not yet support FULL OUTER JOIN. While this is not ideal the post will show how we can get the same result using UNION. A FULL OUTER JOIN allows rows from one table be joined to another and regardless of whether entries exist in either table corresponding the records will be shown.

Let us imagine we have a table called depts which contains a list of departments. Another table emp contains a list of employees including a deptid column to identify the department for which they work:

mysql> create table dept (deptid int auto_increment primary key, dept varchar(100));
Query OK, 0 rows affected (0.03 sec)

mysql> create table emp(empid int auto_increment primary key, name varchar(100), deptid int);
Query OK, 0 rows affected (0.03 sec)

We now want to add information into this table:

mysql> insert into dept(dept) values ('IT'),('Accounting'),('Marketing'),('Sales');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from dept;
+--------+------------+
| deptid | dept       |
+--------+------------+
|      1 | IT         | 
|      2 | Accounting | 
|      3 | Marketing  | 
|      4 | Sales      | 
+--------+------------+
4 rows in set (0.00 sec)

mysql> insert into emp (name, deptid) values ('Tom',1),('Dick',2),('Harry',null);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from emp;
+-------+-------+--------+
| empid | name  | deptid |
+-------+-------+--------+
|     1 | Tom   |      1 | 
|     2 | Dick  |      2 | 
|     3 | Harry |   NULL | 
+-------+-------+--------+
3 rows in set (0.00 sec)

With the information in the tables we now want to show LEFT OUTER JOIN and RIGHT OUTER JOIN results:

mysql> select empid, name, deptid, dept from emp left outer join dept using (deptid);
+-------+-------+--------+------------+
| empid | name  | deptid | dept       |
+-------+-------+--------+------------+
|     1 | Tom   |      1 | IT         | 
|     2 | Dick  |      2 | Accounting | 
|     3 | Harry |   NULL | NULL       | 
+-------+-------+--------+------------+
3 rows in set (0.00 sec)

mysql> select empid, name, deptid, dept from emp right outer join dept using (deptid);
+-------+------+--------+------------+
| empid | name | deptid | dept       |
+-------+------+--------+------------+
|     1 | Tom  |      1 | IT         | 
|     2 | Dick |      2 | Accounting | 
|  NULL | NULL |      3 | Marketing  | 
|  NULL | NULL |      4 | Sales      | 
+-------+------+--------+------------+
4 rows in set (0.00 sec)

To get the FULL OUTER JOIN we simply UNION the 2 previous SQL statements results as follows:

mysql> select empid, name, deptid, dept from emp left outer join dept using (deptid)
    -> union
    -> select empid, name, deptid, dept from emp right outer join dept using (deptid);
+-------+-------+--------+------------+
| empid | name  | deptid | dept       |
+-------+-------+--------+------------+
|     1 | Tom   |      1 | IT         | 
|     2 | Dick  |      2 | Accounting | 
|     3 | Harry |   NULL | NULL       | 
|  NULL | NULL  |      3 | Marketing  | 
|  NULL | NULL  |      4 | Sales      | 
+-------+-------+--------+------------+
5 rows in set (0.00 sec)