I would like to find all users not in a specific group, given the following database schema:

CREATE TABLE foobar_users (
    user_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(20) UNIQUE NOT NULL
);

CREATE TABLE foobar_groups (
    group_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    groupname VARCHAR(20) UNIQUE NOT NULL
);

CREATE TABLE foobar_users_groups (
    user_id INT UNSIGNED NOT NULL,
    group_id INT UNSIGNED NOT NULL,
    PRIMARY KEY(user_id, group_id)
);

There are two common approaches to this problem, one is to use an outer-join and the other approach is to use a sub-select. Here is a left outer join example:

SELECT u.user_id, u.username
FROM foobar_users u
LEFT JOIN (foobar_users_groups ug, foobar_groups g)
  ON u.user_id = ug.user_id
  AND g.group_id = ug.group_id
  AND g.groupname = 'DMV'
WHERE
  ug.user_id IS NULL

Alternatively, you can use a sub-select, e.g.,

SELECT u.user_id, u.username
FROM foobar_users u
WHERE u.user_id NOT IN (
  SELECT ug.user_id
  FROM foobar_groups g, foobar_users_groups ug
  WHERE ug.group_id = g.group_id
    AND g.groupname = 'DMV'
)

My personal preference, and arguably more efficient solution than the above two, is to use a sub-select with NOT EXISTS, as follows:

SELECT u.user_id, u.username
FROM foobar_users u
WHERE NOT EXISTS (
  SELECT NULL
  FROM foobar_groups g, foobar_users_groups ug
  WHERE ug.group_id = g.group_id
    AND ug.user_id = u.user_id
    AND g.groupname = 'DMV'
)