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.
2 Answers
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.
2 Comments
Tim Biegeleisen
@GordonLinoff I will take your upvote as a Valentine's Day present :-)
AyukNayr
Happy valentines! Lol
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;
full outer join, so this is not surprising.