MySQL FULL JOIN not working but RIGHT and LEFT joi

Question

This is driving me nuts. I have two tables that I am attempting to preform a join on, usersXstats and usersXstats_alltime.

Both tables have the same columns: id, userId, statId, and value

What I am trying to do is

SELECT * 
FROM usersXstats 
FULL JOIN usersXstats_alltime 
ON usersXstats.userId=usersXstats_alltime.userId 
AND usersXstats.statId=usersXstats_alltime.statId

However this is returning

Unknown column 'usersXstats.userId' in 'on clause'

This query works just as expected when replacing FULL JOIN with LEFT JOIN, RIGHT JOIN, or INNER JOIN.

To make it easier to read initially I wrote the following query:

SELECT * 
FROM usersXstats as uxs 
FULL JOIN usersXstats_alltime as uxsat 
ON uxs.userId=uxsat.userId 
AND uxs.statId=uxsat.statId

Which returned a different error:

check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULL JOIN usersXstats_alltime as uxsat ON uxs.userId=uxsat.userId AND uxs.statId' at line 1

What on earth am I doing wrong? Thanks in advance!

Answer 1

FULL OUTER JOIN won't support in mysql.

You can emulate FULL OUTER JOIN using UNION (from MySQL 4.0.0 on):

with two tables usersXstats,usersXstats_alltime

SELECT * FROM usersXstats
LEFT JOIN usersXstats_alltime ON usersXstats.userId= usersXstats_alltime.userId
UNION
SELECT * FROM usersXstats
RIGHT JOIN usersXstats_alltime ON usersXstats.statId= usersXstats_alltime.statId

Answer 2

Take a look at this How to simulate FULL OUTER JOIN in MySQL. It may helps.

Answer 3

SELECT Person1.Firstname, Person2.State
FROM Person1
left JOIN Person2
ON Person1.PersonID=Person2.PersonID
UNION
SELECT Person1.Firstname, Person2.State
FROM Person1
right JOIN Person2
ON Person1.PersonID=Person2.PersonID;

is working superbly.

Answer 4

MySQL doesn't support FULL JOIN

http://en.wikipedia.org/wiki/Join_%28SQL%29#Full_outer_join