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:

SELECT
   users_users.userId,
   users_users.realname
FROM
   users_users
INNER JOIN
   users_usergroups
ON
   users_users.userId = users_usergroups.userId
WHERE
   users_usergroups.groupName 
IN ('Adg')

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


    Requires Free Membership to View

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top