获取连接表中不存在的记录

Question

我在 MySQL 数据库中有三个表。第一个表包含用户，第二个表包含项目。下面是这两个的结构。

users
------
userid (int)
username (varchar)

items
------
itemid (int)
name (varchar)

第三个表是连接表。

user_items
----------
userid (int)
itemid (int)

我想要一个查询，该查询返回用户列表以及未分配给他们的项目。

在示例中 我有以下用户

userid      username
1           john
2           tim
3           mark

我还有以下物品

itemid      name
1           book
2           pen
3           backpack

在我的连接表中

userid    itemid
1         1
1         3
2         1
2         2
2         3
3         2

因此，我希望获得不属于用户的项目列表，例如：

userid    itemid
1         2
3         1
3         3

获得这样的结果的最佳查询是什么。我正在尝试一些左连接、左外连接、不在等，但没有成功。

编辑1： 到目前为止我已经尝试过以下查询：

SELECT con.userid, i.itemid FROM items i
LEFT JOIN (
SELECT u.id as userid, ui.itemid
FROM users u
INNER JOIN user_items ui ON u.userid = ui.itemid
) con ON i.itemid = con.itemid
WHERE con.itemid IS NULL

Answer 1

您通常会交叉联接用户和产品以生成所有可能的组合，然后过滤掉桥接表中已存在的关联：

select u.userid, p.itemid
from users u
cross join items i
where not exists (
    select 1 from user_items ui where ui.userid = u.userid and ui.itemid = i.itemid
)

为了提高性能，您需要 user_items(userid, itemid) 上的索引（如果您对这些列有唯一 约束，它应该已经存在）。

我们还可以用反左连接来表达逻辑：

select u.userid, p.itemid
from users u
cross join items i
left join user_items ui on ui.userid = u.userid and ui.itemid = i.itemid
where ui.userid is null