Re: Nested SELECT

>Subject: Nested SELECT >Date: Wed, 4 Oct 1995 08:43:07 -0700 (PDT) >From: Robert Minter <rob@dssmktg.com> >X-Informix-List-Id: <list.7601> > >What we want is to select from one table where there are two or more >of a specific type records in another table ( they are a one-to-many >relationship). For example: > >Table1 ( unique code for every record ) >-------------------------- >code char(10) >name char(50) >status char(10) > >Table2 ( links to Table1 via code, unique (code, type) combination ) >-------------------------- >code char(10) >type char(20) > >Now, what we want, for one example, is to select all from table1 where, >let's say, there is a type of "Type1" and a type of "Type2" in table2. > >Here is our select statement, which is a double nested select statement, >and works. We will need to go 5 deep at some instances. What we want to >know is if this can be optimized any more or attacked at a different >angle: > > SELECT code, name FROM Table1 > WHERE code IN > ( SELECT code FROM Table2 WHERE type = "Type1" AND code IN > ( SELECT code FROM Table2 WHERE type = "Type2" ) ) Interesting! Several possible alternative strategies spring to mind... --Q1-- SELECT T1.Code, T1.Name FROM Table1 T1 WHERE EXISTS(SELECT * FROM Table2 T2 WHERE T2.Code = T1.Code AND T2.Type = 'Type1') AND EXISTS(SELECT * FROM Table2 T2 WHERE T2.Code = T1.Code AND T2.Type = 'Type2') --Q2-- SELECT T1.Code, T1.Name FROM Table1 T1 WHERE 2 = (SELECT COUNT(*) FROM Table2 T2 WHERE T2.Code = T1.Code AND T2.Type IN ('Type1', 'Type2') ) --Q3-- SELECT T1.Code, T1.Name FROM Table1 T1, Table2 T2A, Table2 T2B WHERE T1.Code = T2A.Code AND T2A.Type = 'Type1' AND T1.Code = T2B.Code AND T2B.Type = 'Type2' --Q4-- SELECT T1.Code, T1.Name FROM Table1 T1 WHERE T1.Code IN (SELECT T2.Code FROM Table2 T2 WHERE T2.Type IN ('Type1', 'Type2') GROUP BY T2.Code HAVING COUNT(*) = 2 ); There are undoubtedly others. Of these, I would be most interested in Q2 and Q4. I believe Q4 should be quickest because it avoids correlated sub-queries. Q3 may be quite effective. One issue is how is the list of types available to you? And how are you building the queries? If the list of types could be listed in a table, alternative strategies are possible: CREATE TEMP TABLE L1 (Type ...); INSERT INTO L1 VALUES('Type1'); INSERT INTO L1 VALUES('Type2'); --Q5-- SELECT T1.Code, T1.Name FROM Table1 T1 WHERE Code IN (SELECT T2.Code FROM Table2 T2, L1 WHERE T2.Type = L1.Type GROUP BY T2.Code HAVING COUNT(*) = (SELECT COUNT(*) FROM L1) ); The advantage of Q5 is that the query itself does not alter regardless of how many different types need to be searched for simultaneously. Other strategies could be devised using intermediate temp tables. The benefit or otherwise of these would depend on the context in which the searches are performed. If the search will be performed next type looking for Type1 and Type3, then an intermediate table with the results matching Type1 would speed processing (probably), but if the next search will be for Type999 and Type234, they are unlikely to be of any benefit. Also, with your nested query strategy, you might well get amazingly different results depending on whether you nest them as you wrote it or you reverse the tests on Type1 and Type2. If there are 10000 records of Type1 and 10 records of Type2, your formulation is best; if the ratios are reversed, the query will be dramatically slower. This can be sufficiently significant that it is worth maintaining the statistics on the frequencies of the different types in Table2 so that you can find out which is the best way to build the query. You use the most selective (lowest frequency) types in the innermost SELECT statement. In a similar system using 0.5 million records corresponding to Table1 and about 1.5 million records corresponding to Table2 and with some 50,000 different types, and using up to about 4 different types simultaneously, we were able to change the performance of the query by factors of 100 and more by ordering the types appropriately. The frequency of the different types ranged from 1 to 10,000 or so, and we maintained a table of the frequencies of each type and searched that to find out how to write the main query. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> PS: All SQL completely untested -- take typos as accidents and ignore queries with faults in the logic. PS: Test all queries on big enough databases to be representative, and with both a stopwatch and with SET EXPLAIN.