mysql query?

Question

MySQL

Here such a case happened, if there is a table with users:
table members

id	name
one	User1
2	User2
3	User3
4	User4

a user can have several groups, there is a link
table members_teams table

id	member_id	team_id
one	one	one
2	one	2
3	2	2
4	3	3
5	3	one

Task: Select all users who are not members of the group, for example team_id = 1 The
decision came immediately

SELECT * FROM Members WHERE id NOT IN (SELECT id FROM members_teams WHERE team_id = 1)

But it seems to me that with large numbers of users this is not at all suitable, perhaps there is some other option for solving this problem.

Answer 1

Why is there an id field in the link table?

SELECT Members.* FROM Members LEFT JOIN members_teams ON Members.id = members_teams.member_id and team_id = 1 where members_teams.member_id is null

Answer 2

member is not a user, team is not a group

Answer 3

SELECT m.id 
FROM members m
LEFT JOIN members_teams mt ON m.id = mt.member_id and mt.team_id = 1
WHERE mt.team_id is null

Answer 4

I would use an OUTER JOIN because a user who is not a member of any group satisfies the "Not a member of group 1" requirement, however with an inner join these users will not be returned at all.

Answer 5

It's better to use JOIN. Something like this:

SELECT Members.* FROM Members LEFT JOIN members_teams ON Members.id = members_teams.member_id WHERE team_id <> 1

Answer 6

select members.id, members.name from members
inner join members_teams on members.id = members_teams.member_id
where members_teams.team_id  NOT IN (1)

In haste, in general, it makes sense to use the JOIN operator

Answer 7

SELECT m.id FROM members m LEFT JOIN members_teams mt ON m.id = mt.member_id and mt.team_id = 1 WHERE mt.team_id is null

Answer 8

SELECT m.id, sum(CASE mt.team_id = 1 WHEN TRUE THEN 1 ELSE 0 END) as in_team FROM members m LEFT JOIN members_teams mt ON mt.member_id = m.id GROUP BY m.id
this query will give a table of all users, and for each will be specified in_team (0 - not in a group, 1 - in a group)
use it as a subquery