[thelist] an elegant correlated sub-query that doesn't want to

Matt Warden mwarden at gmail.com
Thu Apr 16 15:18:01 CDT 2009


On Thu, Apr 16, 2009 at 3:24 PM, Sarah Adams <sarahwbs at gmail.com> wrote:
> I was thinking this might return a full set of all set/option combinations:
>
>  SELECT o.OptionID
>       , CONCAT(s.NameDE,': ',o.NameDE) AS SetOption
>    FROM OptionSets AS s
>  INNER
>    JOIN Options AS o
>      ON o.SetID = s.SetID
>  GROUP
>      BY o.OptionID
>
> I'm hoping the optimizer can figure it out.

It will only operate on those records which match the join condition.
This includes the concat() function and the grouping. You can verify
this by running an explain on the query and looking at the order of
operations.

-- 
Matt Warden
Cincinnati, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.



More information about the thelist mailing list