A subquery is a query expression that can be used in
expressions:
The subquery must return a single-column derived table with a maximum of one row. The value of the subquery is then the value in the derived table or the NULL value if the derived table is empty.predicates:
In the predicates ANY, SOME, ALL, IN and EXISTS the subquery returns a derived table.In the FROM clause of SELECT expressions:
The subquery returns a derived table.In join expressions:
The subquery returns a derived table.
A subquery is always enclosed in parentheses.
Correlated subquery
In a nested query expression, the inner subquery is referred to as a correlated subquery if it references columns in a table which is used in an outer query expression.
Correlated subqueries are evaluated by evaluating the inner subquery for all the rows in the table of the outer query. Non-correlated subqueries are only evaluated once as they do not depend on the outer query.