[development] Creative querying

[larry at garfieldtech.com]

Also be aware that, at least on MySQL, subqueries in a WHERE clause get 
executed for every otherwise-matching record.  Depending on your dataset 
that could eat up a lot of time.  Subqueries in a SELECT or FROM clause 
are only executed once, so those are fine.

With a proper join against a weighting table, though, the subquery 
shouldn't even be necessary.

--Larry Garfield

Ken Winters wrote:
> select * from users where uid in (select uid from users_roles where rid 
> in (4,5,6,7,8));
> 
> Note that I dropped the rid column from the results, but your query 
> didn't provide all that useful results for it to start with (picked 
> one).  If you actually need it then you may still need a join, and also 
> might need something like group_concat.
> 
> - Ken Winters
> 
> On Nov 23, 2009, at 10:41 AM, Brian Vuyk wrote:
> 
>> 1.) We are including extra data from the users table (wasn't central 
>> to the question, so I omitted it to simplify)
>> 2.) How would you structure this exactly with subquery?
>>
>> Brian
>>
>> Ken Winters wrote:
>>> On Nov 23, 2009, at 10:18 AM, Brian Vuyk wrote:
>>>>
>>>> SELECT DISTINCT u.uid, ur.rid FROM {users} u RIGHT JOIN 
>>>> {users_roles} ur ON ur.uid = u.uid WHERE rid = 6 OR rid = 8 OR rid = 
>>>> 5 OR rid = 7 OR rid = 4 GROUP BY uid;
>>>>
>>>> Brian
>>>
>>> 1) Why are you doing a join when all the info you are selecting is in 
>>> the users_roles table?  If you don't need it for some other reason, 
>>> problem solved.
>>> 2) I've found it's generally better to use subqueries (where X in 
>>> (select Y from Z)) rather than join and group.
>>>
>>> - Ken Winters
>>
>