Full Join In Mysql Fails

Question

I am trying to learn Joins. But when I tried Full join on employee and salary table it fail. Why is it failing?

I have added the results of the solution posted just for reference.

mysql> select * from employee;
+-----+---------+
| eid | ename   |
+-----+---------+
| 1   | sampath |
| 2   | maclean |
| 3   | sudheer |
+-----+---------+
3 rows in set (0.00 sec)

mysql> select * from Salary;
+------+--------+------+
| sid  | salary | eid  |
+------+--------+------+
| 1001 |   5000 | 2    |
| 1002 |  70000 | 3    |
| 1003 |  70000 | 3    |
+------+--------+------+
3 rows in set (0.00 sec)

mysql> select employee.eid, employee.ename, Salary.salary from employee JOIN Salary  on employee.eid=Salary.eid;
+-----+---------+--------+
| eid | ename   | salary |
+-----+---------+--------+
| 2   | maclean |   5000 |
| 3   | sudheer |  70000 |
| 3   | sudheer |  70000 |
+-----+---------+--------+
3 rows in set (0.00 sec)

mysql> select employee.eid, employee.ename, Salary.salary from employee LEFT JOIN Salary  ON employee.eid=Salary.eid;
+-----+---------+--------+
| eid | ename   | salary |
+-----+---------+--------+
| 1   | sampath |   NULL |
| 2   | maclean |   5000 |
| 3   | sudheer |  70000 |
| 3   | sudheer |  70000 |
+-----+---------+--------+
4 rows in set (0.00 sec)

mysql> select employee.eid, employee.ename, Salary.salary from employee RIGHT JOIN Salary  ON employee.eid=Salary.eid;
+------+---------+--------+
| eid  | ename   | salary |
+------+---------+--------+
| 2    | maclean |   5000 |
| 3    | sudheer |  70000 |
| 3    | sudheer |  70000 |
+------+---------+--------+
3 rows in set (0.00 sec)

mysql> select employee.eid, employee.ename, Salary.salary from employee FULL JOIN Salary ON employee.eid=Salary.eid; ERROR 1054 (42S22): Unknown column 'employee.eid' in 'field list' mysql>

Update: Following is the result of Left Join Union Right Join

mysql> SELECT * FROM employee e
    -> LEFT JOIN Salary s ON e.eid = s.eid
    -> UNION
    -> SELECT * FROM employee e
    -> RIGHT JOIN Salary s ON e.eid = s.eid;
+------+---------+------+--------+------+
| eid  | ename   | sid  | salary | eid  |
+------+---------+------+--------+------+
| 1    | Rai     | NULL |   NULL | NULL |
| 2    | pinto   | 1001 |  50000 | 2    |
| 3    | sudheer | 1002 |  70000 | 3    |
| 3    | sudheer | 1003 |  70000 | 3    |
+------+---------+------+--------+------+
4 rows in set (0.00 sec)

possible duplicate of [Full Outer Join in MySQL](http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql) — , Feb 07 '15 at 07:13

Lucky · Accepted Answer · 2015-02-07T07:33:25.860

2

MySQL does not support FULL OUTER JOIN keyword. Only LEFT JOIN and RIGHT JOIN are supported.

You can try this to emulate the same:

SELECT * FROM employee e
LEFT JOIN salary s ON e.eid = s.eid
UNION
SELECT * FROM employee e
RIGHT JOIN salary s ON e.eid = s.eid

edited Feb 07 '15 at 07:33

answered Feb 07 '15 at 07:26

Lucky

16,787
19
117
151

It is not a substitute for full join. – Maclean Pinto Feb 08 '15 at 09:25
Yes you right it's not a substitute but it's the most probable workaround to reproduce the result in mysql for full join queries.. – Lucky Feb 08 '15 at 14:19
You can read this SO post for why mysql doesn't support it...http://stackoverflow.com/questions/3362079/is-there-a-reason-mysql-doesnt-support-full-outer-joins – Lucky Feb 08 '15 at 14:24

score 1 · Answer 2 · edited May 23 '17 at 10:26

1

mysql doesn't support full join.

check the below question hope it will help you to solve your problem :)

MySQL FULL JOIN not working but RIGHT and LEFT join works

edited May 23 '17 at 10:26

Community

1
1

answered Feb 07 '15 at 07:22

Tariq hussain

614
5
11

Full Join In Mysql Fails

2 Answers2