Subqueries can return single values (scalar), multiple columns or multiple rows, or both. Subqueries can be used in a SELECT clause elements list, the FROM clause, the WHERE clause, the ORDER BY clause, an INSERT statement VALUES clause, an UPDATE statement set clause, or a CASE statement expression. Also tables and views can be created using subqueries.
The WITH clause allows prepared execution of subquery results. Results can then be utilized by the primary calling query:
WITH query1 AS (subquery), query2 AS (subquery) SELECT * FROM query1 JOIN query2 JOIN query3;
Hierarchical queries allow hierarchical representations of hierarchical data. The hierarchy can be accessed from the root node or a starting point within the hierarchy (the START WITH clause). The CONNECT BY clause allows linking between a column in the current row and another value in a parent (the PRIOR operator) row:
SELECT <column>, LEVEL FROM <table> START WITH <condition> CONNECT BY <current_row> <parent_column> = PRIOR <parent_row> <current_column>;
Flashback queries allow a query flashback to a specific point in time, based on a timestamp or an SCN:
SELECT * FROM ... AS OF {TIMESTAMP|SCN};
A Top-N query can be used to retrieve a small number of rows from a large row set.
SELECT * FROM (SELECT * FROM <table> ORDER BY ...) WHERE ROWNUM < n;