>which is faster, a "subselect" or "exists"? > >or does it matter? > >i've shy'ed away from sub-selects because of speed issues (fears)?
It is wise to prefer EXISTS(<subselect>) over IN(<subselect>). Often, they will have a very similar performance in Firebird due to the optimizer replacing the IN with an EXISTS under the hood (though there at least used be cases where that couldn't be done) and I don't think there are any cases where IN(<subselect>) will be faster than EXISTS(<subselect>). EXISTS(<subselect>) is typically OK if there are indexes for the subselect making that query fast and other parts of the where clause limiting the number of times the EXISTS has to be executed. In your case, I assume there to be a very limited number (a few thousand rows is very limited, a few million rows isn't) of cases with t.PlantKey='20030319103909097704' (which you may have indexed?) and that could make your query with the EXISTS have good performance. However, a query like SELECT * FROM AMillionRowsTable A CROSS JOIN AnotherMillionRowsTable B WHERE EXISTS(SELECT 1 FROM SomeTable C WHERE A.PK = C.FK AND B.PK = C.FK) (CROSS JOIN links each row in the left table to each row of the right table) will (of course) be unbearably slow, since the EXISTS has to be performed at least 1 000 000 000 000 times. HTH, Set
