ERROR: FULL JOIN is only supported with merge-joinable join conditions

Question

when i try to join tables with inner join..it returns the data..but when i join 4 tables with full outer join then i says

ERROR: FULL JOIN is only supported with merge-joinable join conditions"

An example query:

SELECT hr_employee.name,hr_movement.amount,hr_concept.name,hr_period.name
FROM hr_employee
FULL OUTER JOIN hr_movement
ON hr_employee.ad_client_id=hr_movement.ad_client_id
FULL OUTER JOIN hr_concept
ON hr_movement.ad_client_id=hr_employee.ad_client_id
FULL OUTER JOIN hr_period
ON hr_concept.ad_client_id=hr_employee.ad_client_id

Answer 1

SELECT hr_employee.name,hr_movement.amount,hr_concept.name,hr_period.name
FROM hr_employee
FULL OUTER JOIN hr_movement
ON hr_employee.ad_client_id=hr_movement.ad_client_id
FULL OUTER JOIN hr_concept
ON hr_movement.ad_client_id=hr_employee.ad_client_id /*<- here*/
FULL OUTER JOIN hr_period
ON hr_concept.ad_client_id=hr_employee.ad_client_id /*<- and here*/

You're joining tables, but don't use these tables in the join condition. Apart from that MySQL doesn't support FULL OUTER JOINs. They are realized like in this answer.

Answer 2

There is no FULL OUTER JOIN in MySQL. See 7.2.12. Outer Join Simplification and 12.2.8.1. JOIN Syntax:

You can emulate FULL OUTER JOIN using UNION (from MySQL 4.0.0 on):

with two tables t1, t2:

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id

with three tables t1, t2, t3:

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
LEFT JOIN t3 ON t2.id = t3.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
LEFT JOIN t3 ON t2.id = t3.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
RIGHT JOIN t3 ON t2.id = t3.id

Source

Take a look at here