Syntax error in full outer join?

Question

I have a query where I am using a full outer join. But in some instance, it gives me a syntax error. What could be the reason for this? I don't see any miscode in my query.

Answer 1

MySQL does not support full outer join, but you can simulate it as a union between a left and right join query:

SELECT * FROM pbsdev3.item t1
LEFT JOIN pbsdev3.item_ledger_entry t2 ON t1.No_ = t2.Item_No_
UNION ALL
SELECT * FROM pbsdev3.item t1
RIGHT JOIN pbsdev3.item_ledger_entry t2 ON t1.No_ = t2.Item_No_
WHERE t1.No_ IS NULL

Note that in general if you find yourself doing full outer joins often, it could imply that your keys and data model are not well defined. One reason why MySQL does not support full joins could be that you should not have to use it.

Answer 2

Full outer join is quite a pain in MySQL. The first thing I would note is that it should not be needed. The items should match in the two tables, so an inner join or left join should be sufficient:

SELECT i.*, ile.*
FROM pbsdev3.item i LEFT JOIN
     pbsdev3.item_ledger_entry ile
     ON i.No_ = ile.Item_No_;

If you really need full outer join, then gather together all the items and use left join:

select it.*, ile.*
from (select i.No_ from item i union
      select ile.Item_No_ from item_ledger_entry ile
     ) i left join
     item it
     on it.No_ = i.No_ left join
     item_ledger_entry ile
     on ile.No = i.No;