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.