Correlated subqueries

A correlated subquery is a subquery that refers to a column of a table that is not listed in its FROM clause. The column can be in the Projection clause or in the WHERE clause. To find the table to which the correlated subquery refers, search the columns until a correlation is found.

In general, correlated subqueries diminish performance. Use the table name or alias in the subquery so that there is no doubt as to which table the column is in.

The database server will use the outer query to get values. For example, if the table taba has the column col1 and table tabb has the column col2 and they contain the following:

taba.col1       aa,bb,null
tabb.col2       bb, null

And the query is:

select * from taba where col1 in (select col1 from tabb);

Then the results might be meaningless. The database server will provide all values in taba.col1 and then compare them to taba.col1 (outer query WHERE clause). This will return all rows. You usually use the subquery to return column values from the inner table. Had the query been written as:

select * from taba where col1 in (select tabb.col1 from tabb);

Then the error -217 column not found would have resulted.

The important feature of a correlated subquery is that, because it depends on a value from the outer SELECT, it must be executed repeatedly, once for every value that the outer SELECT produces. An uncorrelated subquery is executed only once.