To continue reading for free, register below or login
To read more you must become a member of SearchOracle.com
If you do a search for "correlated subquery" on the Web, you will
find several sites suggesting that:
A correlated subquery is a subquery
that is evaluated once for each row of the outer query.
It may indeed be convenient to imagine a correlated subquery as
being "evaluated" or "executed" once for each row of the outer query;
do not, however, be misled into thinking that the database optimizer
actually executes it in that fashion, because this will lead you to
believe that it's not very efficient, when in fact most optimizers
will process a correlated subquery very efficiently, as a join.
Okay, with that out of the way, what's the difference between an ordinary
subquery and a correlated subquery? The correlated subquery makes an actual
reference, using a correlation variable, to the outer query.
For example, here's a query with two subqueries:
select studentname
, studentmark
, ( select avg(studentmark)
from students
where class = t1.class ) as classaverage
, ( select avg(studentmark)
from students ) as schoolaverage
from students t1
In the above example, t1 is the correlation variable
that lets the correlated subquery refer to the table in the outer query.
For each student, two averages are calculated: the average mark
of all students in the same class, and the overall average of all
students in all classes.
Here's another example of a correlated subquery,
this time in the WHERE clause:
select category
, articletitle
, articlepubdate
from articles zz
where articlepubdate
= ( select max(articlepubdate)
from articles
where category = zz.category )
In this example, only the most recent article in each category is selected.
For More Information
|
