Menu
Is there a reason MySQL doesn't support FULL OUTER JOINS? I've tried full outer join syntax in mysql many times and it never worked, just found out its not supported by mysql so just curious as to why?
956
MySQL does not support full outer join out of the box, unlike other databases such as PostgreSQL, and SQL Server. So you will need to do a full outer join using a combination of other join types such as LEFT JOIN ad RIGHT JOIN that are supported in MySQL.
You're getting that error because MySQL does not support (or recognize) the FULL OUTER JOIN syntax. However, it is possible emulate a FULL OUTER JOIN in MySQL. We actually need two queries. One query return all the rows from the table on the left.
MySQL does not support FULL JOIN, so you have to combine JOIN, UNION and LEFT JOIN to get an equivalent. It gives the results of A union B. It returns all records from both tables. Those columns which exist in only one table will contain NULL in the opposite table.
Another type of join is called a MySQL RIGHT OUTER JOIN. This type of join returns all rows from the RIGHT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).
MySQL lacks a lot of functionality that other databases have*. I think they have a pretty huge backlog of ideas and not enough developers to implement them all.
This feature was requested in 2006 and is still not implemented. I guess it has low priority because you can work around it by combining LEFT and RIGHT OUTER JOIN with a UNION ALL. Not pleasant, but it does the trick. Change this:
SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.table2_id = table2.id to this:
SELECT * FROM table1 LEFT JOIN table2 ON table1.table2_id = table2.id UNION ALL SELECT * FROM table1 RIGHT JOIN table2 ON table1.table2_id = table2.id WHERE table1.table2_id IS NULL * To be fair to MySQL, they also have some features that many other databases don't have.
182
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
