What is the difference between correlated subquery and subquery

Sagar Jaybhay Updated date Apr 13, Subqueries are enclosed in parentheses. Subquery is also called an inner query and the query which encloses that inner query is called an outer query. Subqueries are nested, when the subquery is executed first,and its results are inserted into Where clause of the main query.

Correlated subqueries are the opposite case, where the main query is executed first and the subquery is executed for every row returned by the main query. The levels to which you can do the nesting is implementation-dependent. Correlated Subquery A Correlated Subquery is one that is executed after the outer query is executed.

So correlated subqueries take an approach opposite to that of normal subqueries. The correlated subquery execution is as follows: -The outer query receives a row. Vertica Analytics Platform Version 9. A noncorrelated subquery executes independently of the outer query. The subquery executes first, and then passes its results to the outer query, For example:.

Vertica executes this query as follows:. A correlated subquery typically obtains values from its outer query before it executes. When the subquery returns, it passes its results to the outer query. In the following example, the subquery needs values from the addresses. Was this topic helpful? In normal subqueries the inner queries are executed first and then the outer query is executed but in Correlated Subquery outer query is always dependent on inner query so first outer query is executed then inner query is executed.

The result of correlated subquery determines whether the fetched row should be the part of our output results. The Subqueries are called as nested subqueries when another subquery is used in where or having condition of the Outer Query. The Execution of Nested suubquery always follows bottom up approach. A scalar sub-query expression is a sub-query that returns exactly one column value from one row. What if the oracle failed to return scalar sub-query?

There are some specific conditions. Select D.