Difference between Subquery, Nested Subquery and Correlated Subquery

Query: - Query can be defined as a way to inquire the data from the database. It is used to extract the data from one table or multiple tables depending upon the user needs.

Subquery:-If a sql statement contains another sql statement then the sql statement which is inside another sql statement is called Subquery. It is also known as nested query. The Sql Statement which contains the other sql statement is called Parent Statement.

Nested Subquery:-If a Subquery contains another subquery, then the subquery inside another subquery is called nested subquery.

Correlated Subquery:-If the outcome of a subquery is depends on the value of a column of its parent query table then the Sub query is called Correlated Subquery.

or

A correlated subquery is a subquery that uses values from the outer query, requiring the inner query to execute once for each outer query 

The Oracle database wants to execute the subquery once and use the results for all the evaluations in the outer query. With a correlated subquery, the database must run the subquery for each evaluation because it is based on the outer query’s data.

select

   book_key,

   store_key,

   quantity

from

   sales s

where

  quantity <
   (select max(quantity)

       from sales

       where book_key = s.book_key); 

BOOK_K STOR QUANTITY

------ ---- ----------

B101 S101 1000

B102 S102 10

B102 S103 200

…

B116 S105 100

B101 S105 8000

B109 S109 100

81 rows selected.

In the example above, the subquery references the book_key in the outer query. The value of the book_key changes by row of the outer query, so the database must rerun the subquery for each row comparison. This has a significant performance impact on the execution time of the query, and for that reason, correlated subqueries should be avoided if possible.

The outer query knows nothing about the inner query except its results. For that reason, the outer query cannot reference any columns in the subquery. However, the subquery has access to the outer query and can reference outer query columns, thus the correlated subquery.