MySQL FULL JOIN不起作用，但RIGHT和LEFT联接有效 [英] MySQL FULL JOIN not working but RIGHT and LEFT join works

问题描述

这真让我发疯.我有两个要在其上进行联接的表，usersXstats和usersXstats_alltime.

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

两个表都具有相同的列:id，userId，statId和value

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

我想做的是

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

但是这又回来了

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

当用LEFT JOIN，RIGHT JOIN或INNER JOIN替换FULL JOIN时，此查询可以正常工作.

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!

推荐答案

MySQL不支持FULL JOIN

MySQL doesn't support FULL JOIN

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

这篇关于MySQL FULL JOIN不起作用，但RIGHT和LEFT联接有效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！