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

返回不同的错误:

检查与您的 MySQL 服务器版本相对应的手册，了解在第 1 行的FULL JOIN usersXstats_alltime as uxsat ON uxs.userId=uxsat.userId AND uxs.statId"附近使用的正确语法

我到底做错了什么?提前致谢！

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屋！