Correlated subqueries

In a nested query expression, an inner subquery is called a correlated subquery if it references columns of an outer table, i.e. a table that is used in one of the outer query expressions.

You can use correlated subqueries to determine the relationships between the values in a column.

Example

In a personnel table with a column for the age of each person, you can determine which people are exactly the average age (see example below).

Uncorrelated subqueries only need be evaluated once. Correlated subqueries must be evaluated several times for the various rows of the outer table. If the subquery is nested, the innermost subquery is evaluated first, etc.

Examples

The following query is a correlated subquery:

SELECT DISTINCT order_text FROM orders WHERE EXISTS

(SELECT * FROM service WHERE service.order_num = orders.order_num)

The inner subquery in the WHERE clause references the column ORDER_NUM in the ORDERS table of the outer query. ORDERS.ORDER_NUM is also known as an outer reference, since the column references a table in the outer query. The query is evaluated by determining the value of ORDERS.ORDER_NUM in the first row of the ORDERS table, evaluating the subquery on the basis of this value and using this result in the outer query. This is then repeated for the second value of
ORDERS.ORDER_NUM and so on. The query returns a derived table:

order_text

Staff training

Database draft customers

Instruction concerning mail merge

For each order in the SERVICE table, you want to select the services whose price is above the average service price for this order:

SELECT s1.service_num, s1.order_num, s1.service_total*s1.service_price 
   FROM service s1
   WHERE s1.service_total*s1.service_price > 
   (SELECT AVG (s2.service_total*s2.service_price) FROM service s2 WHERE 
   s1.order_num=s2.order_num)

Query expressions can be nested to any depth:

SELECT company, cust_num FROM customers WHERE cust_num IN
   (SELECT cust_num FROM orders WHERE order_num IN
       (SELECT order_num FROM service WHERE (service_price*service_total) IN
           (SELECT MAX(service_price*service_total) FROM service)))

Since these are not correlated subqueries, each subquery is evaluated once and the result is then used in the outer query.

Derived table

company	cust_num
Foreign Ltd.	106