What is correlated subquery in sql oracle

Correlated sub-query

Correlated sub-query is a sub-query that executes once for each outer query value (or record).

Let us assume a query with outer and inner queries. The inner query is co-related sub-query if the WHERE clause of the inner query is evaluated for each record of the outer query.

Let us suppose that the outer query have 100 records, then the inner sub-query has to evaluate for 100 times.

Example:

SELECT car_model, showroom, total_cars_sold FROM car_sales c WHERE total_cars_sold < (SELECT max(total_cars_sold) FROM car_sales WHERE car_model = c.car_model);

This query finds the car_model, showroom name, and total_cars_sold of all showrooms other than the one sold the maximum cars for each car model. The inner query is evaluated to ‘car_model = c.car_model’. So, the inner query has to be evaluated for each record of the outer query result. Hence, the inner query is correlated sub-query.

Car_model	Showroom	Total_cars
I20	ABC	200
I10	ABC	100
I20	XYZ	205
I10	XYZ	29

The query given above will produce the following result for the table instance given above;

Car_model ---------------	Showroom ----------------	Total_cars ---------------
I20	ABC	200
I10	ABC	100
I20	XYZ	205
I10	XYZ	29

Example for non-correlated sub-query:

SELECT Name, Weight FROM Student WHERE Weight > (SELECT Weight FROM Student WHERE Name = ‘Mathews’);

In this query, the sub-query evaluates only once and finds the weight of student ‘Mathews’. It does not find the weight of ‘Mathews’ for every outer query record.

On the other hand, the correlated sub-query explained above evaluates once for each outer query record.

Some notes:

Correlated sub-queries usually correspond to normal join queries, i.e. join queries that have no sub-queries, and can usually be expressed as such.

It is sometimes recommended that they are translated into such join queries, because most SQL DBMSs will execute them faster.

Certain correlated sub-queries can correspond to normal set queries rather than join queries, due to the comparator used with the correlated sub-query.

Note that whether a sub-query is correlated or not has solely to do with the tables referenced in the sub-query, and nothing to do with the kind of comparator used with .it

************

Go to Database Glossary - DBMS Important Keywords page

What is correlated sub-query

Correlated sub-query examples

Compare correlated subquery with non-correlated subquery

Drawback of correlated subquery

Define correlated sub-query