Correlated Queries

When processing a correlated query, the outer query is processed first. The server compares the previous value to the current value to see if the inner query needs to be executed again. For example, given the following query to find the supplier who supplies the largest quantity of each product:

SELECT sno, pno FROM partsupp p
    WHERE qty = 
        SELECT MAX(quantity) FROM items 
            WHERE pno = p.pnoORDER BY pno

For each record in the PARTSUPP table, the inner query is evaluated to find the maximum quantity. If the next PARTSUPP record is for the same product, the maximum quantity is already known, and therefore the subquery is not performed. If PNO is indexed, then adding the ORDER BY PNO clause makes the query faster because the records with the same PNO values are all grouped together.

A correlated query whose correlated query block always evaluates to false is detected and does not continue to execute. If the correlation condition is not the condition that causes the query block to be false, the query block is always false.

For example, in this query:

SELECT * FROM table1 x 
    WHERE EXISTS 
        SELECT * FROM table2 y,table3 z 
            WHERE y.f1 = z.f1 
              AND x.f2 = z.f2 
              AND y.f3 > 10;

if the inner SELECT does not retrieve any records without evaluating the correlation condition (X.F2=Z.F2), it can be determined that the query will not return any records; no records match the non-correlated criteria.

If only one correlation condition exists, the previous value of the correlation will unconditionally be saved for comparison with the next correlated value. (In previous versions of the XDB Server, this was only saved and compared if the correlated condition was not embedded in a NOT, AND or OR condition.)