0

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>

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.