取得連接表中不存在的記錄

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