A query within a query is a powerful mechanism to retrieve additional information. The subquery can appear in any type of SQL statement, including SELECT, INSERT, UPDATE, and DELETE. It can be used in any part of SQL statement clauses, including WHERE, FROM, and SELECT list.
There are two types of subqueries: correlated and noncorrelated. The former refers to a syntax in which the subquery references the outer query, and the latter represents a subquery that is independent of the outer query. A number of operators can be used to evaluate results returned by the subquery to the outer query, such as IN, EXISTS, ANY, SOME, and ALL. Subqueries can be used with the GROUP BY… HAVING clause in conjunction with aggregate functions.
Often, a subquery can be replaced with an equivalent JOIN syntax, which might result in performance gain. There is no practical limit on the nesting level for the subqueries, either correlated or noncorrelated, although some RDBMSs do impose limits.