Using Correlated Subqueries
Go Up to Application Design Techniques
Subqueries are SELECT statements which are included as a clause or expression within another statement. They are typically used to generate a value or result set that are used in conditions of the superior query.
A correlated subquery is one in which the conditions of the subquery are different for each row in the parent query, because they depend on values that y from row to row. InterBase executes the subquery many times, once for each row in the parent query. Evaluating each row has a large cost in performance relative to a non-correlated subquery. InterBase optimizes non-correlated subqueries out of the loop, executes once, and uses the result as a fixed dataset.
Example as correlated subquery:
SELECT * FROM DEPARTMENT D WHERE EXISTS (SELECT * FROM EMPLOYEE E WHERE E.EMP_NO = D.MNGR_NO AND E.JOB_COUNTRY = 'England')
Example as join:
SELECT D.* FROM DEPARTMENT D JOIN EMPLOYEE E ON D.MNGR_NO = E.EMP_NO WHERE E.JOB_COUNTRY = 'England'
InterBase optimizer executes a non-correlated subquery once, and uses the result set as many times as necessary in the parent query.
Sometimes a correlated subquery is necessary, given the semantics of the SQL language. However, these types of queries should be used with care and with the understanding that their performance is geometric in relation to the size of the dataset on which they operate.