MySQL FULL JOIN 不起作用，但 RIGHT 和 LEFT join 起作用

发布于 2024-10-09 00:43:13 字数 830 浏览 0 评论 0原文

这让我抓狂。我尝试对两个表执行联接，userXstats 和 usersXstats_alltime。

两个表都有相同的列： id、userId、statId 和 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 时，此查询按预期工作。

为了更容易阅读，我最初编写了以下查询：

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

返回了不同的错误：

检查与您的 MySQL 服务器版本相对应的手册，以获取在 'FULL JOIN usersXstats_alltime as uxsat ON uxs.userId= 附近使用的正确语法uxsat.userId AND uxs.statId' at line 1

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

原文

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!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

隐诗 2024-10-16 00:43:13

MySQL 不支持 FULL JOIN

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

回复收藏 0 原文

橘香 2024-10-16 00:43:13

看看这个如何在MySQL中模拟FULL OUTER JOIN。
这可能有帮助。

回复收藏 0 原文

倾城°AllureLove 2024-10-16 00:43:13

mysql 不支持FULL OUTER JOIN。

您可以使用UNION（从 MySQL 4.0.0 开始）模拟FULL OUTER JOIN：

使用两个表 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

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

回复收藏 0 原文

在梵高的星空下 2024-10-16 00:43:13

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;

工作得很好。

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.

回复收藏 0 原文

攀登最高峰 2024-10-16 00:43:13

我不知道问题是什么，但我遇到了同样的问题，因此您可以尝试以下操作：

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

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

回复收藏 0 原文

~没有更多了~