Q

What is a correlated subquery?

What is subquery and correlated subquery? What is the difference between them?

What is subquery and correlated subquery? What is the difference between them?
 

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


 

This was first published in March 2004

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close