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.
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.
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)