SQL: How to find rows in one table that have no references to rows in another tables?-CodePudding

I have three tables: users, rooms, room_users. Users can have many rooms and rooms as well can have many users, so this is many to many relationship.

users table:

 ---- ----------- ----- 
| id |   name    | age |
 ---- ----------- ----- 
| 1  | Christian | 19  |
| 2  | Ben       | 36  |
| 3  | Robert    | 52  |
| 4  | Monica    | 25  |
| 5  | Alice     | 26  |
| 6  | William   | 18  |
 ---- ----------- -----

rooms table:

 ---- ---------- 
| id |   name   |
 ---- ---------- 
| 1  | College  |
| 2  | Work     |
| 3  | Football |
 ---- ----------

And room_users table that represents relationship between users and rooms:

 --------- --------- 
| user_id | room_id |
 --------- --------- 
| 1       | 1       |
| 1       | 3       |
| 2       | 2       |
| 4       | 1       |
| 5       | 2       |
| 6       | 1       |
| 6       | 3       |
 --------- ---------

So, having these tables we can say that:

Christian(1) belongs to College(1) and Football(3) rooms.
Ben(2) belongs to Work(2) room.
Robert(3) does not belong to any room.
Monica(4) belongs to College(1) room.
Alice(5) belongs to Work(2) room.
William(1) belongs to College(1) and Football(3) rooms.

And now if I want to find users (ids) that does belong to Football room I should use this query:

SELECT user_id FROM room_users WHERE room_id = 3

Output for this query:

 --------- 
| user_id |
 --------- 
| 1       |
| 6       |
 ---------

This is correct, only Christian(1) and William(3) belong to Football room.

But how to find users that does NOT belong to Football room? In this case, query must return 2, 3, 4 and 5 ids. That is, all IDs excluding IDs from the first query.

Is it possible to do it using LEFT JOIN? As far as I know, it is more efficient way than using sub-queries.

Thanks in advance!

EDIT:

I've found a query that can solve the problem, but this query is VERY SLOW on large database:

SELECT users.id FROM users WHERE 0=(SELECT COUNT(*) FROM room_users WHERE user_id=users.id AND room_id=3);

CodePudding user response：

Without correlated behavior, try something like this:

SELECT u.*
  FROM users AS u
  LEFT JOIN (
          SELECT DISTINCT user_id FROM room_users WHERE room_id = 3
       ) AS v
    ON v.user_id = u.id
 WHERE v.user_id IS NULL
;

For performance issues, start by reviewing the explain/execution plan and use of indexes.

CodePudding user response：

You could find those users that belong to the football room AND then exclude those using not in. Also you can use a JOIN

SELECT u.*
FROM
users u
WHERE user_id NOT IN
(SELECT user_id FROM room_users WHERE room_id=3)

CodePudding user response：

You are correct that this is possible to do with a left join.

SELECT
    u.id
FROM
    users u
LEFT JOIN room_users ur
ON u.id = ur.user_id
AND ur.room_id = 3
WHERE
    ur.room_id is null;