[thelist] SQL - select in a join?

Matt Warden mwarden at gmail.com
Tue Jan 10 19:02:34 CST 2006


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Hey Ben,

Ben Prew wrote:
> The extra columns are there so you can use them in the upper select statement.
> 
> ie
> 
> select a1.first_name, a1.last_name, a1.Intrl_Wrk_GROUP
> from
> some_table g
> left join (
>    (
>        SELECT A.Agent_Id,A.First_Name,A.Last_Name,A.My_Rep_Id,
>        VALUE
>            (
>                AD.Wrk_GROUP_Id,A.Intrl_Wrk_GROUP
>            ) AS Intrl_Wrk_GROUP
>        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

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


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


- --
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-----



More information about the thelist mailing list