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.
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
