MySQL FULL JOIN not working but RIGHT and LEFT join works

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!

score 21 · Accepted Answer · answered Dec 25 '10 at 20:09

21

MySQL doesn't support FULL JOIN

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

answered Dec 25 '10 at 20:09

Mantar

2,710
5
23
30

1

Replacing FULL JOIN with FULL OUTER JOIN returns the error: check the manual that corresponds to your MySQL server version for the right syntax to use near 'OUTER JOIN usersXstats_alltime ON usersXstats.userId=usersXstats_alltime.userId ' at line 1 – Alex Dec 25 '10 at 20:14
Read the full section in the link, a MySQL solution is provided. – Mantar Dec 25 '10 at 20:17
Oh, stupid me didn't read far enough. I thought the first example was MySQL. Thanks! – Alex Dec 25 '10 at 20:19

Jahan Zinedine · Answer 2 · 2010-12-25T20:18:12.033

6

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

edited Dec 25 '10 at 20:18

answered Dec 25 '10 at 20:05

Jahan Zinedine

14,616
5
46
70

Thanks, I was able to get the results I wanted using UNION, however doesn't this seem a bit messy? Shouldn't I be able to do this with a full outer join? – Alex Dec 25 '10 at 20:16
Nevermind. I guess MySQL doesn't support FULL JOIN. Thank you for your answer – Alex Dec 25 '10 at 20:19
Please add the infos to your post in case when the link dies – Black Nov 15 '19 at 08:50

score 1 · Answer 3 · answered Jan 21 '12 at 05:44

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

score 0 · Answer 4 · edited Jul 04 '13 at 21:00

0

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.

edited Jul 04 '13 at 21:00

Undo

25,519
37
106
129

answered Jul 04 '13 at 20:40

rajesh

181
1
4

Hi Rajesh - if you add 4 spaces to the start of each line in the code then it will be properly formatted as a code block :) – robjohncox Jul 04 '13 at 21:00

score 0 · Answer 5 · edited Mar 19 '21 at 10:22

0

I don't know what the problem is but I was facing the same issue so here you can try this:

SELECT * 
FROM usersXstats 
Left JOIN usersXstats_alltime 
ON usersXstats.userId=usersXstats_alltime.userId
Union
SELECT * 
FROM usersXstats 
RightJOIN usersXstats_alltime 
ON usersXstats.userId=usersXstats_alltime.userId

edited Mar 19 '21 at 10:22

Syscall

19,327
10
37
52

answered Mar 19 '21 at 10:15

Ankit Pandey

106
4

1

Welcomo to SO. You answer equal to other already given. https://stackoverflow.com/a/8950981/1138946. – CFreitas Mar 19 '21 at 16:26

MySQL FULL JOIN not working but RIGHT and LEFT join works

5 Answers5

Linked

Related