Ask the Expert

Users not in a specific group

This has got to be a simple problem, but I'm a beginner in SQL. I need to generate a list of users that are not members of a specific group. I can easily get a list of users who are part of a group, but I can't seem to figure out the inverse.

I've got two tables, users_users and users_groupnames. The following SQL will get me a list of users in the Adg group:

   users_users.userId = users_usergroups.userId
IN ('Adg')

How do I get a list of users who are not part of the group Adg? Thanks!

    Requires Free Membership to View

There are two ways to do it. The first is to start with a list of all the userids that are in the Adg group in the usergroup table, and then use this list to filter the users from the user table, so that you are left with users that aren't. You can do this in a single query with a NOT EXISTS subquery:

select userid
     , realname
  from users_users
 where userid NOT IN
       ( select userid
           from users_usergroups
          where groupname in ('Adg')  

Some databases (actually only one that I know of, but it's extremely popular -- MySQL prior to version 4.1) do not support subqueries, so there's an alternate method that works just as nicely, using a LEFT OUTER JOIN:

select U.userid
     , U.realname
  from users_users U
left outer
  join users_usergroups UG
    on U.userId = UG.userId  
   and            UG.groupname in ('Adg') 
 where UG.userId IS NULL

Essentially, you start with the rows in the left table, all of which will be returned in a LEFT OUTER join, whether there are matching rows from the right table or not. Then in the join's ON clause, you attempt to match to the specific usergroup rows for 'Adg'. In any LEFT OUTER join, if no match is found, the columns from the right table on that particular row will be null. These are the rows you want, and the WHERE clause effectively filters all the other rows out.

Some people have trouble seeing the ON condition requiring a match on a key, and then testing the same key for NULL in the WHERE clause, but it does make sense, and it simply takes advantage of the way outer joins work. Note that if the groupname in ('Adg') condition were placed in the WHERE clause and not the ON clause, the results would not be correct.

For More Information

This was first published in January 2004

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: