SQL View - Embedded Select vs Join

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