Nested and Correlated Subqueries

Nested - The subquery executed first and the result is inserted to the main query.

SELECT *
  FROM t11_executed_orders t11
 WHERE t11.t11_symbol IN
           (SELECT aa.t11_symbol
              FROM t11_executed_orders aa
             WHERE aa.t11_value = (SELECT bb.t11_price * bb.t11_filled_volume
                                     FROM t11_executed_orders bb
                                    WHERE bb.t11_exec_id = '123'));

the inner most query will executed first and then the nest subquery and after that the main query.


Correlated - The main query executed first and the subquery is executed against each and every row returned by the main query.

UPDATE t11_executed_orders t11
   SET t11.t11_value =
           (SELECT a.t11_price * a.t11_filled_volume
              FROM t11_executed_orders a
             WHERE     t11.t11_exec_id = a.t11_exec_id
                   AND t11.t11_exectime_int = a.t11_exectime_int
                   AND t11.t11_side = a.t11_side
                   AND t11.t11_exchange = a.t11_exchange)
 WHERE t11.t11_value <>
           (SELECT a.t11_price * a.t11_filled_volume
              FROM t11_executed_orders a
             WHERE     t11.t11_exec_id = a.t11_exec_id
                   AND t11.t11_exectime_int = a.t11_exectime_int
                   AND t11.t11_side = a.t11_side
                   AND t11.t11_exchange = a.t11_exchange);



the same update can be written in PL-SQL as well . like this.

BEGIN
    FOR i IN (SELECT *
                FROM (SELECT t11_exec_id,
                             t11_exchange,
                             t11_side,
                             t11_datetime,
                             t11_symbol,
                             t11_routingac,
                             t11_price * t11_filled_volume
                                 AS calculated_t11_value,
                             t11_value
                        FROM mubasher_oms.t11_executed_orders)
               WHERE calculated_t11_value <> t11_value)
    LOOP
        UPDATE mubasher_oms.t11_executed_orders
           SET t11_value = i.calculated_t11_value
         WHERE     t11_exec_id = i.t11_exec_id
               AND t11_exchange = i.t11_exchange
               AND t11_side = i.t11_side;
    END LOOP;
END;

for more infor

http://sql-plsql.blogspot.com/2011/09/difference-nested-correlated-subquery.html