Difference between CORRELATED and UNCORRELATED subqueries in DB2

The subquery is a nested query. When this subquery is executed only once and the result of this subquery is used to extract the data in the main query, then this type of subquery is known as UNCORRELATED subquery. On the other hand, when a subquery refers to the main query for each execution, then the subquery is known as CORRELATED subquery.

For example, if we want to extract all the orders from the ORDERS table which have ORDER_TOTAL more than overall average, then we can use the below UNCORRELATED subquery.

Example

SELECT ORDER_ID FROM ORDERS WHERE ORDER_TOTAL > (SELECT AVG(ORDER_TOTAL) FROM ORDERS)

There is a DB2 table ORDER_CHANNEL which stores the channels through which orders can be placed like ONLINE, AGENT, AFFILIATE, etc.

If we want to extract the order with the maximum amount for each channel, then we will use below CORRELATED subquery.

Example

SELECT ORDER_ID, ORDER_CHANNEL, ORDER_TOTAL
   FROM ORDERS T1
WHERE ORDER_TOTAL IN (SELECT MAX (T2.ORDER_TOTAL)
   FROM ORDERS T2 WHERE
   T1.ORDER_CHANNEL_ID = T2.CHANNEL_ID
   GROUP BY T2.CHANNEL_ID)