[thelist] SQL - select in a join?

Ben Prew ben.prew at gmail.com
Tue Jan 10 19:46:25 CST 2006


On 1/10/06, Matt Warden <mwarden at gmail.com> wrote:

> Could you offer any insight as to why one would add the projection in
> the inline view rather than simply selecting the entire tuple and
> projecting only once in the outer select? e.g.:
>
> select a1.first_name, a1.last_name, a1.Intrl_Wrk_GROUP,
>         VALUE
>            (
>                AD.Wrk_GROUP_Id,A.Intrl_Wrk_GROUP
>            ) AS Intrl_Wrk_GROUP
> from
> some_table g
> left join (
>    (
>        SELECT *
>        FROM AGENT_MST A
>        LEFT JOIN Agent_Dtl AD ON A.Agent_Id = AD.Agent_Id AND
> AD.Eff_Till IS NULL
>    ) A1
> on g.w_agent_id = a1.agent_id

Matt,

The biggest reason I would cite for doing it in the inline view is
because the data is most likely only relevant to the inline view. 
Most of the time, doing things in inline views is for readability and
collecting related items together.

However, since the table AD and the table AS come from somewhere else
in the query, I don't know why it was done that way.  Perhaps the
inline view (A1) was used to filter somewhere else in the query where
it would make more sense to have that information together.

>
> Does it have to do with memory usage during the outer (as in where it's
> nested) join operation?
>

As far as performance reasons go for making a decision like that, I
don't know much about tuning queries in anything besides Oracle and a
little postgres.  Also, its hard to tell with only that snippet of the
query, but its possible that by doing the filter there, its possible
it was the place that had the most rows to go over, not that VALUE
(aka coalesce) is an expensive call, though.

There's a remote possibility that everything in the select statements
are part of indexes at that point, so the query would only have to
look at the indexes and never actually go to the table blocks to
retrieve the data, again, its hard to say without knowing more about
the environment.

I think that it, like most decisions, probably came down to a matter of style.

>
>
> - --
> Matt Warden
> Miami University
> Oxford, OH, USA
> http://mattwarden.com
>
>
> This email proudly and graciously contributes to entropy.
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.1 (GNU/Linux)
> Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
>
> iD8DBQFDxFkqrI3LObhzHRMRAr41AJ4m3446Uz7CckbCbPbrGB8WOq9dzQCfWq42
> J9W4+CbWEucVKZiiM5oog0w=
> =ODBd
> -----END PGP SIGNATURE-----
> --
>
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
>
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !
>


--
--Ben



More information about the thelist mailing list