They allow for quick formulation of extremely complex SQL statements.Oracle's introduction of scalar subquery support is another example of the company's commitment to keeping pace with the evolution of the SQL language.The scalar subquery in Listing D is quite useful for Oracle data warehouse applications.
All syntax is available for both correlated and uncorrelated queries, except that the clause cannot be used with an uncorrelated subquery. Depending on the syntax, the subquery might be rewritten to an outer join, semi join, cross join, or anti join.
The CTE result set is derived from a simple query and is referenced by UPDATE statement.
Common table expressions can also be used with the SELECT, INSERT, DELETE, and CREATE VIEW statements.
A query is processed differently depending on whether the subquery calls any aggregation functions.
The syntax for row values and the circumstances in which row values can be used are illustrated in examples below.