Dapatkan rekod yang tidak wujud dalam jadual bersambung

Question

Saya mempunyai tiga jadual dalam pangkalan data MySQL. Jadual pertama mengandungi pengguna dan jadual kedua mengandungi projek. Di bawah adalah struktur kedua-dua ini.

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

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

Jadual ketiga ialah jadual gabungan.

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

Saya mahukan pertanyaan yang mengembalikan senarai pengguna dan projek yang tidak diberikan kepada mereka.

Dalam contoh Saya mempunyai pengguna berikut

userid      username
1           john
2           tim
3           mark

Saya juga ada item berikut

itemid      name
1           book
2           pen
3           backpack

Dalam jadual penyertaan saya

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

Jadi saya ingin mendapatkan senarai item yang tidak dimiliki oleh pengguna, contohnya:

userid    itemid
1         2
3         1
3         3

Apakah pertanyaan terbaik untuk mendapatkan hasil seperti ini. Saya mencuba beberapa sambung kiri, sambung luar kiri, sambung kiri, dsb. tanpa berjaya.

Sunting 1: Setakat ini saya telah mencuba pertanyaan berikut:

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

Anda biasanya akan 交叉联接pengguna dan produk untuk menjana semua kombinasi yang mungkin, kemudian tapis keluar perkaitan yang sudah wujud dalam jadual jambatan:

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
)

Untuk meningkatkan prestasi, anda memerlukan user_items(userid, itemid) 上的索引（如果您对这些列有唯一 kekangan, yang sepatutnya sudah wujud).

Kita juga boleh menggunakan songsang 左连接 untuk menyatakan logik:

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