Q

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!


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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close