#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2002
    Posts
    155
    Rep Power
    13

    Performance question - left join VS. sub-query


    Hi all. I'm trying to join three tables, and I can do it either using a left join or a sub-query, and I'm wondering which is the better choice.

    Here are the tables:


    Code:
     
    cms_users (this table has more columns, but u_id and username are all I'm querying here)
    
     u_id | username |
    ------+----------+
        1 | admin    |
        2 | vanadric |
        3 | lindav   |
    
    
    cms_groups
    
     g_id |      name       | status 
    ------+-----------------+--------
        3 | /               |      1
       11 | Communications  |      1
       10 | Human Resources |      1
    
    
    cms_group_membership
    
     g_id | u_id | clearance 
    ------+------+-----------
        3 |    1 |         1
       11 |    5 |         1
    I have a interface through which I control the settings for each group, and one option in that interface is to add a new user to the selected group.

    For that I need a query which will return all users who do not belong to the group in question.

    Here's the query using a left join:
    Code:
    select u.u_id, u.username
    from   cms_users u left join cms_group_membership gm on u.u_id = gm.u_id
    where  gm.g_id != 3 /* or whatever g_id has been passed to the interface */
    or     gm.g_id is null;
    And here's the query using a sub-query:
    Code:
    select u.u_id, u.username
    from   cms_users u
    where  u.u_id not in (select gm.u_id
    		      from   cms_group_membership gm
    		      where  gm.g_id = 3) /* or whatever g_id has been passed to the interface */
    What I'm wondering is if there is an advantage to using one of these methods over the other?

    With the second there's the obvious fact that two queries are being executed, so would this be detrimental if there were a large number of records in the group membership table which the sub-query hits (which is quite likely)?

    Thanks in advance,
    Pablo.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,377
    Rep Power
    391
    There is no general answer to that type of question. It depends on the DBMS, SQL does not decree that queries are executed in a certain way. All that matters is what data is returned not how the data is retrieved.

    You have to run tests with both methods and compare.

IMN logo majestic logo threadwatch logo seochat tools logo