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

Matt Warden mwarden at gmail.com
Thu Apr 16 13:04:08 CDT 2009

On Thu, Apr 16, 2009 at 1:46 PM, Sarah Adams <sarahwbs at gmail.com> wrote:
>> i didn't read matt's link to tom kyte's blog, because although tom is highly
>> respected, he pretty much only covers oracle (~ptui~)
> Yes, sorry, this is in MySQL, not Oracle ;)

The point was that it explained why you can't simply use a varchar
field with a list of values as a list of values in your SQL.

And, Rudy, before you go knocking Oracle... the only reason there is a
problem in the first place is that the data model has non-atomic
values. I am a bit surprised that MySQL has built-in functions to
support this.

> Now, do you suppose that MySQL will be smart enough not to do full-table
> scans for this bit of the query? I'll still use it either way, I'm just
> curious.
>     JOIN ( 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 ) AS q

Sarah, I'm not sure what part you are getting at. I don't see anything
here that would trigger a full table scan.

Matt Warden
Cincinnati, OH, USA

This email proudly and graciously contributes to entropy.

More information about the thelist mailing list