mysql 即使第二个表上的行不存在也进行右联接

6jygbczu 于 2023-06-28 发布在 Mysql

关注(0)|答案(3)|浏览(122)

我想连接两个表，即使第二个表上没有匹配项。
表用户：

uid | name
1     dude1
2     dude2

表帐户：

uid | accountid | name
1     1           account1

我想要的table：

uid  | username | accountname
1      dude1      account1
2      dude2      NULL

我正在尝试的查询：

SELECT user.uid as uid, user.name as username, account.name as accountname
FROM user RIGHT JOIN account ON user.uid=accout.uid

我得到的是：

uid  | username | accountname
1      dude1      account1

3条答案

使用Left Join代替

SELECT user.uid as uid, user.name as username, account.name as accountname
FROM user LEFT JOIN account ON user.uid=account.uid

尝试使用LEFT JOIN查询

SELECT user.uid as uid, user.name as username, account.name as accountname
FROM user 
LEFT JOIN account 
ON user.uid=accout.uid

我想让你们看一下JOIN query的可视化表示

右联接保留第二个表中的所有结果（它保留右侧表中的所有结果），您需要左联接，或者交换联接子句中user和account的位置。

SELECT user.uid as uid, user.name as username, account.name as accountname
FROM user LEFT JOIN account ON user.uid=account.uid

我想应该可以了。