Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL FULL JOIN not working but RIGHT and LEFT join works

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!

like image 308
Alex Avatar asked Dec 25 '10 19:12

Alex


2 Answers

MySQL doesn't support FULL JOIN

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

like image 90
Mantar Avatar answered Oct 01 '22 20:10

Mantar


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

like image 23
Jahan Zinedine Avatar answered Oct 01 '22 19:10

Jahan Zinedine