[newbie] Can I avoid this subquery... and do I need to?

jmpfffc · #1 (**permalink**) 06-07-07, 09:03

I want to select all users not beloning to a certain usergroup, OR not being in the usergroup-table at all. I have the following query which does exactly that:

($usergroup is the group where I want to see users from that are NOT in it)

Code:

SELECT	*
FROM		`users`
WHERE	`users`.`userid`
NOT IN (SELECT `users`.`userid` FROM `usergroups` WHERE `usergroups`.`groupid` = '".$usergroup."')

I'm pretty new at this and I'm not sure if the above query is the smart way of doing this.

r937 · #2 (**permalink**) 06-07-07, 11:36

Code:

SELECT users.*
  FROM users
LEFT OUTER
  JOIN usergroups
    ON usergroups.userid = users.id
   AND usergroups.groupid = $usergroup
 WHERE usergroups.userid IS NULL

jmpfffc · #3 (**permalink**) 06-08-07, 06:30

Thanks r937. Can you tell me which of the two is the preffered way? Is there any difference at all?

r937 · #4 (**permalink**) 06-08-07, 06:50

if i offer an opinion about a preference, it would only be my personal opinion

best would be for you to test both and decide which you prefer

jmpfffc · #5 (**permalink**) 06-08-07, 07:19

I found no difference in execution time with my current (small) test data, next week I get all the data that is supposed to go in the database, so I will give it a shot then.

But I do value your personal opinion, because in my situation I have no collegues or friends who are busy with this kind of thing, so I can never have a true exchange of ideas, a conversation. I usually read tutorials, but they also reflect the author's opinion. Many times I come across a different tutorial that has a different view, and for now I lack the insight to judge which of the tutorials would be the best.

guelphdad · #6 (**permalink**) 06-08-07, 08:16

from what I've read on various forums, the outer join is supposed to perform more quickly than the subquery.

Internet Services

Web Development

Digital Marketing

Consumer Tech