Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2007
    Posts
    12

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

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT users.*
      FROM users
    LEFT OUTER
      JOIN usergroups
        ON usergroups.userid = users.id
       AND usergroups.groupid = $usergroup
     WHERE usergroups.userid IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Posts
    12
    Thanks r937. Can you tell me which of the two is the preffered way? Is there any difference at all?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2007
    Posts
    12
    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.

  6. #6
    Join Date
    Mar 2004
    Posts
    480
    from what I've read on various forums, the outer join is supposed to perform more quickly than the subquery.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •