Eliminating correlated subqueries

In some situations, you may need the ability to compare a particular column value with a maximum or minimum value. Often you form these queries as nested queries involving a correlated attribute (also known as an outer reference). As an example, consider the following query, which lists all orders, including product information, where the product quantity-on-hand cannot cover the maximum single order for that product:

SELECT o.ID, o.OrderDate, p.*
FROM SalesOrders o, SalesOrderItems s, Products p
WHERE o.ID = s.ID AND s.ProductID = p.ID
       AND p.Quantity < ( SELECT MAX( s2.Quantity )
                         FROM SalesOrderItems s2
                         WHERE s2.ProductID = p.ID )
ORDER BY p.ID, o.ID;

The graphical plan for this query is displayed in the Plan Viewer as shown below. Note how the query optimizer has transformed this nested query to a join of the Products and SalesOrders tables with a derived table, denoted by the correlation name DT, which contains a window function.

Rather than relying on the optimizer to transform the correlated subquery into a join with a derived table—which can only be done for straightforward cases due to the complexity of the semantic analysis—you can form such queries using a window function:

SELECT order_qty.ID, o.OrderDate, p.*
  FROM ( SELECT s.ID, s.ProductID,
           MAX( s.Quantity ) OVER ( 
             PARTITION BY s.ProductID
             ORDER BY s.ProductID ) 
           AS max_q
           FROM SalesOrderItems s )
  AS order_qty, Products p, SalesOrders o
  WHERE p.ID = ProductID 
    AND o.ID = order_qty.ID 
    AND p.Quantity < max_q
  ORDER BY p.ID, o.ID;

See also

• MIN function [Aggregate]
• MAX function [Aggregate]