Get members of a given group from N:M relationship

I have this N:M relationship:

CREATE TABLE auth_user (
    id integer NOT NULL PRIMARY KEY,
    username character varying(150) NOT NULL UNIQUE
);

CREATE TABLE auth_group (
    id integer NOT NULL PRIMARY KEY,
    name character varying(80) NOT NULL UNIQUE
);

CREATE TABLE auth_user_groups (
    id integer NOT NULL PRIMARY KEY,
    user_id integer REFERENCES auth_user(id) NOT NULL,
    group_id integer REFERENCES auth_group(id) NOT NULL,
    CONSTRAINT user_groups UNIQUE(user_id, group_id)
);

INSERT INTO auth_user VALUES (1, 'user1');
INSERT INTO auth_user VALUES (2, 'user2');
INSERT INTO auth_group VALUES (1, 'group1');
INSERT INTO auth_group VALUES (2, 'group2');
INSERT INTO auth_user_groups VALUES (1, 1, 1);
INSERT INTO auth_user_groups VALUES (2, 2, 1);
INSERT INTO auth_user_groups VALUES (3, 2, 2);

How to select all usernames which are in the group 'group1'?

I use PostgreSQL, but SQL which works everywhere is preferred.

This EXISTS query returns unique users, regardless of whether there can be duplicate entries in auth_user_groups.

SELECT *
FROM   auth_user u
WHERE  EXISTS (
   SELECT 1
   FROM   auth_user_groups
   WHERE  user_id = u.id
   AND    group_id = (SELECT id FROM auth_group WHERE name = 'group1')
   );

Typically fast, too.
Notes

For Postgres - which you seem to be using.

The sequence of index columns matters. You defined UNIQUE(user_id, group_id), which is implemented with a corresponding unique index. For your particular query, an index on (group_id, user_id) would be preferable.

You can either switch the columns of the UNIQUE constraint, or create an additional (optionally unique) index with the columns reversed if you need both (which is the common case). Related:

• Is a composite index also good for queries on the first field?

Also be aware that columns included in a UNIQUE constraint can still be NULL. (Unlike a PRIMARY KEY constraint which makes all member columns NOT NULL automatically!) You'd typically want user_id and group_id in auth_user_groups to be NOT NULL as well. Related:

• How to implement a many-to-many relationship in PostgreSQL?

StackExchange Database Administrators Q#202254, answer score: 8