Full Join Not working

Question

And here is my Query :

select employee.eid, staff.acc_no from employee FULL JOIN staff on employee.eid = staff.eid

I can able to do join, left join, right join but not full join. It says the error

Unknown column 'employee.eid' in 'field list': select employee.eid, staff.acc_no from employee FULL JOIN staff on employee.eid = staff.eid

What is the mistake i am doing ??

possible duplicate of [Full Outer Join in MySQL](http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql) — jpw, Oct 31 '14 at 12:35

score 2 · Accepted Answer · edited May 23 '17 at 11:50

2

As @Niels pointed out firstly you dont have full joins in mysql.

I would recommend you to read this.

However you can emulate outer join with the help of a UNION operator.

The detailed explanation could be found here as well

edited May 23 '17 at 11:50

Community

1
1

answered Oct 31 '14 at 12:37

Avinash Babu

6,171
3
21
26

Gordon Linoff · Answer 2 · 2014-10-31T14:37:59.463

1

MySQL does not support full outer join. However, your tables should have proper foreign key relationships, so it should not be necessary:

select employee.eid, staff.acc_no
from employee INNER JOIN
     staff
     on employee.eid = staff.eid;

If they don't, you can use the union all/group by approach:

select eid, max(accno) as accno
from (select e.eid, NULL as acc_no from employee e union all
      select s.eid, s.acc_no from staff
     ) se
group by eid;

edited Oct 31 '14 at 14:37

answered Oct 31 '14 at 12:50

Gordon Linoff

1,242,037
58
646
786

Awesome, alternate way +1 – SA__ Oct 31 '14 at 12:57
@gordon Linoff this wont work ..check the second code writeup – Avinash Babu Oct 31 '14 at 15:40

Full Join Not working

2 Answers2