Correlated Sub-Queries What’s wrong with them?
Consider the statement:
update customers set stat = “A”
where exists (
select “X”
from orders o
where o.custid = customer.custid
and o.cmpny = customers.cmpny
and o.stat = “OPEN” )
The statement is executed by executing the sub-query, on orders, for every row retrieved from customers.
If customers table had 100,000 rows, the sub-query would get executed 100,000 times.
However, if orders only had 20 rows with stat=“OPEN” the database would be doing a lot of extra work.