Get records that do not exist in the connected table

Question

I have three tables in a MySQL database. The first table contains users and the second table contains items. Below is the structure of these two.

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

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

The third table is the connection table.

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

I want a query that returns a list of users and the projects that are not assigned to them.

In the example I have the following user

userid      username
1           john
2           tim
3           mark

I also have the following items

itemid      name
1           book
2           pen
3           backpack

In my join table

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

So I want to get a list of items that are not owned by the user, for example:

userid    itemid
1         2
3         1
3         3

What is the best query to get such a result. I'm trying some left joins, left outer joins, left joins, etc. without success.

Edit 1: So far I have tried the following queries:

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

You would typically cross-join users and products to generate all possible combinations, then filter out associations that already exist in the bridge table:

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
)

To improve performance, you need an index on user_items(userid, itemid) (if you have unique constraints on these columns, it should already exist).

We can also use reverse left join to express logic:

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