Correlated subqueries

A correlated subquery is a subquery that refers to a column of a table that is not in its FROM clause. The column can be in the Projection clause or in the WHERE clause.

In general, correlated subqueries diminish performance. It is recommended that you qualify the column name in subqueries with the name or alias of the table, in order to remove any doubt regarding in which table the column resides.

The following query is an example of a correlated subquery that returns a list of the 10 latest shipping dates in the orders table. It includes an ORDER BY clause after the subquery to order the results because (except in the FROM clause) you cannot include ORDER BY within a subquery.

Figure 1: Query

SELECT po_num, ship_date FROM orders main
   WHERE 10 >
      (SELECT COUNT (DISTINCT ship_date)
         FROM orders sub
         WHERE sub.ship_date < main.ship_date)
   AND ship_date IS NOT NULL
   ORDER BY ship_date, po_num;

The subquery is correlated because the number that it produces depends on main.ship_date, a value that the outer SELECT produces. Thus, the subquery must be re-executed for every row that the outer query considers.

The query uses the COUNT function to return a value to the main query. The ORDER BY clause then orders the data. The query locates and returns the 16 rows that have the 10 latest shipping dates, as the result shows.

Figure 2: Query result

po_num     ship_date

4745       06/21/1998
278701     06/29/1998
429Q       06/29/1998
8052       07/03/1998
B77897     07/03/1998
LZ230      07/06/1998
B77930     07/10/1998
PC6782     07/12/1998
DM354331   07/13/1998
S22942     07/13/1998
MA003      07/16/1998
W2286      07/16/1998
Z55709     07/16/1998
C3288      07/25/1998
KF2961     07/30/1998
W9925      07/30/1998

If you use a correlated subquery, such as Query, on a large table, you should index the ship_date column to improve performance. Otherwise, this SELECT statement is inefficient, because it executes the subquery once for every row of the table. For information about indexing and performance issues, see the HCL OneDB™ Administrator's Guide and your HCL OneDB Performance Guide.

You cannot use a correlated subquery in the FROM clause, however, as the following invalid example illustrates:

SELECT item_num, stock_num FROM items, 
   (SELECT stock_num FROM catalog 
       WHERE stock_num = items.item_num) AS vtab;

The subquery in this example fails with error -24138:

ALL COLUMN REFERENCES IN A TABLE EXPRESSION MUST REFER
TO TABLES IN THE FROM CLAUSE OF THE TABLE EXPRESSION.

The database server issues this error because the items.item_num column in the subquery also appears in the Projection clause of the outer query, but the FROM clause of the inner query specifies only the catalog table. The term table expression in the error message text refers to the set of column values or expressions that are returned by a subquery in the FROM clause, where only uncorrelated subqueries are valid.