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
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in January 2004