Re: Correlated Subquries

Hi, It seems to me that query 3-32b is confusing, and the same query is used in both the 7.1 and 7.2 tutorials. It is written: SELECT UNIQUE Manu_Name, Lead_Time FROM Stock, Manufact WHERE Manufact.Manu_Code IN (SELECT Manu_Code FROM Stock WHERE Description MATCHES '*shoe*') AND Stock.Manu_Code = Manufact.Manu_code; Since both Manu_Name and Lead_Time come from the Manufact table, there is no need to reference the Stock table in the outer portion of the SELECT statement, and no need for the join either; it should be written: SELECT Manu_Name, Lead_Time FROM Manufact WHERE Manu_Code IN (SELECT Manu_Code FROM Stock WHERE Description MATCHES '*shoe*'); Note that because there is no join with the Stock table, there is no need for the UNIQUE keyword (though it would do no harm). The published version requires the UNIQUE as otherwise, each manufacturer is listed as many times as there are stock items listed for the manufacturer in the Stock table. Consequently, this revised version is not only simpler to understand; it is also quicker to execute. I've copied the Informix documentation team with this message. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> >From: Cosmo Lee <cosmo@echonyc.com> >Date: Wed, 06 Nov 1996 19:15:10 -0800 >X-Informix-List-Id: <news.30173> > >Can anyone suggest a good source for understanding Correlated >Subqueries? I've been reading the "Informix Guide to SQL: Tutorial >V7.1". Specifically, on page 3-36 is a subquery that I can't seem to >de-construct satisfactorially in a way that makes sense to me. The >manual doesn't do much to actually explain in detail how these >subqueries work. > >Subqueries have always been a sticky thing for me. Sometimes I >understand what is happening, but at other times I look at something and >I have no idea how it moves from point A to point B. > >Any suggestions appreciated - Thanks.