Re: SQL View - Embedded Select vs Join

Note that one of the capabilities of the SQL query engine is the
ability to re-write a statement into an equivilent but better
performing one. For example, converting from embedded selects into
JOINs

Since you say that you saw the same results for both statement in VE,
it's likely that the version with the embedded selects is being
re-written.

HTH,
Charles


On Thu, May 14, 2009 at 12:09 PM, Luis Rodriguez
<luis.rodriguez2@xxxxxxxxx> wrote:

Hi,

Let's suppose three files:

----------
File1:
CustomerName, State_Code1, State_Code2, Cust_Code1, Cust_Code2

File2:
State_Code, State_Name (Primary Key: State_Code)

File3:
Cust_Code, Cust_Description (Primary Key: Cust_Code)
----------

I've been asked to write a view that shows the name or description for each one of the FileA codes.

As I see it, I can write the view as sequence of join files:

-----------------
SELECT A.*,  B.State_Name as State_Name1, C.State_Name as State_Name2,
            D.Cust_Description as Cust1, E.Cust_Description as Cust2
FROM FileA as A

LEFT OUTER JOIN  File1, File2 B on A.State_Code1 = B.State_Code
LEFT OUTER JOIN  File1, File2 C on A.State_Code1 = C.State_Code
LEFT OUTER JOIN  File1, File3 D on A. Cust_Code1= C. Cust _Code
LEFT OUTER JOIN  File1, File3 E on A. Cust_Code1= E. Cust _Code
-----------------

OR, I can write it using a Select for each “name” field:

-----------------
Select A.*,
(Select State_Name from File2 b where A.State_Code1 = B.State_Code) As State_Name1,
(Select State_Name from File2 c where A.State_Code2 = C.State_Code) As State_Name2,
…
etc., etc,

FROM File1 as A
-----------------------

Which option would you think is more efficient? Interestingly enough, a small test I did with only one field yielded exactly the same results on Visual Explain.

Thanks in advance,


Luis Rodriguez

IBM Certified Systems Expert
eServer i5 iSeries Technical Solutions




--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.