full outer join not working

Question

I Want to do a full outer join on 2 tables but I get this error, what am I doing wrong?

mysql> select * from cajas full outer join almacenes on almacenes.codigo = cajas.almacen;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'outer join almacenes on almacenes.codigo = cajas.almacen' at line 1 mysql>

Answer 1

So far as I know, you can't do a full outer join in MySQL, so just run it as the UNION of a LEFT JOIN and a RIGHT JOIN as

SELECT * FROM 
cajas LEFT JOIN almacenes ON almacenes.codigo = cajas.almacen
UNION
SELECT * FROM 
cajas RIGHT JOIN almacenes ON almacenes.codigo = cajas.almacen;

I think this would fix your problem.

EDIT: After a quick Googling, I realized that the above may not be perfect for all cases. Instead you could do

SELECT * FROM 
cajas LEFT JOIN almacenes ON almacenes.codigo = cajas.almacen
UNION ALL
SELECT * FROM 
cajas RIGHT JOIN almacenes ON almacenes.codigo = cajas.almacen
WHERE cajas.almacen IS NULL;

See this blog post for reference.