Subqueries¶
Subqueries come in different shapes and forms: scalar subqueries, nested (single- or multi-row) subqueries (in the WHERE
clause), correlated subqueries, inline views (in the FROM
clause), and factored subqueries (in the WITH
clause), which are also known as common table expressions (CTEs) outside Oracleland.
They are pretty versatile constructs as they can appear in almost any clause of DML statements with the exception of the GROUP BY
clause.
Many of the subquery types are interchangeable.
Inline subqueries can be rewritten as factored subqueries, and factored subqueries can often be rewritten as inline subqueries; recursive factored subqueries are the exception to this rule.
Recursive factored subqueries can, nevertheless, typically be written as hierarchical queries using the CONNECT BY ... START WITH ...
syntax.
Similarly, you can write a correlated subquery as a join, but not every join can become a correlated subquery.