When working with many-to-many relationships, you often have a pivot table linking two entities — for example, members and groups. Each record in this table represents a link between a member and a group.

Sometimes, you want to find all members not linked to a specific group. For instance, imagine you want the list of member IDs for all members that are not part of group ID 1.

The example setup

Let’s say you have the following pivot table:

CREATE TABLE member_groups (
  member_id bigint,
  group_id bigint
);

Each record represents one membership link.

The problem

You want to get all member_id values for members that don’t have a link with group_id = 1.

The cleanest solution: NOT EXISTS

SELECT DISTINCT mg.member_id
FROM member_groups mg
WHERE NOT EXISTS (
  SELECT 1
  FROM member_groups mg2
  WHERE mg2.member_id = mg.member_id
    AND mg2.group_id = 1
);

This query returns all distinct member_ids that never appear in a row with group_id = 1.

Why it’s good:

• Works efficiently on large datasets
• Avoids unnecessary grouping
• Reads clearly: “select members where no record exists for group 1”

Alternative using GROUP BY

SELECT member_id
FROM member_groups
GROUP BY member_id
HAVING SUM(CASE WHEN group_id = 1 THEN 1 ELSE 0 END) = 0;

Here, we group by each member and only keep those where the count of group 1 links is zero.

Another option with LEFT JOIN

SELECT DISTINCT mg.member_id
FROM member_groups mg
LEFT JOIN member_groups mg1
  ON mg.member_id = mg1.member_id AND mg1.group_id = 1
WHERE mg1.member_id IS NULL;

This version is easy to understand but can be less efficient on large tables.

Conclusion

When filtering out associations in a many-to-many relation, the NOT EXISTS pattern is usually the most readable and performant option. It clearly expresses intent: “Give me all members that don’t belong to group 1.”