Subqueries in Oracle


Subqueries return one value or a set of values for use in comparison purposes. A subquery using a single-row comparison operator returns a single value. A subquery can not use ORDER BY or GROUP BY clauses. A subquery must return a single value if used in an equality comparison. If used with IN, the subquery can return multiple values.

Regular Subqueries

A regular subquery is a subquery in which the result of a subquery is used to evaluate the main query predicate and to retrieve rows for display.

SELECT * FROM TABLEA WHERE COLUMNA IN (SELECT COLUMNB FROM TABLEB);

Correlated Subqueries

A correlated subquery is the same as a regular subquery except that a new result is produced for each row of the table that is evaluated. A correlated subquery is executed using table aliases. A table alias is a referenceable name for a table between the main query and subqueries. The table alias links main query and subqueries.

SELECT * FROM TABLEA A WHERE EXISTS(SELECT * FROM TABLEB WHERE COLUMNB = A.COLUMNA);

Predicates in Subqueries

Predicates compare expressions and always evaluate to true, false or null. Numerous comparison operators can be used to form predicates. Predicates can also be combined to form logical operators. Precedence rules determine the order in which predicates are executed when using multiple predicates. Precedence is over-ridden by parentheses. The following predicate types can be used in an SQL statement WHERE clause. All predicates can be negated by the use of the NOT operator.

Note that the AND and OR logical operators' precedence can be controlled by the use of parentheses in order to determine precedence.

AND NOT (<predicate> OR <predicate>)
WHERE NOT (<predicate> AND <predicate>)

The EXISTS and NOT EXISTS predicates can be used to produce good efficiency in queries containing subqueries. The logical expression EXISTS is true if the subquery returns at least one row. It is false if the subquery is not. Use the NOT EXISTS predicate to make sure that tables are properly loaded and refer to valid data.

WHERE [NOT] EXISTS (subquery)

Complex queries, subqueries and correlated subqueries may take a long time to run. Test complex queries initially with small sets of data in order to estimate results against larger data sets. Note that use of the [NOT] EXISTS predicate can often be much faster than that of a simple multi-table join.

Using Subqueries in Complex Queries

Any predicates combined with operators can contain a subquery. Subqueries can also be nested.

SELECT * FROM TABLEA A WHERE EXISTS
	(SELECT * FROM TABLEB B WHERE COLUMNB = A.COLUMNA AND WHERE EXISTS
		(SELECT * FROM TABLEC WHERE COLUMNC = B.COLUMNB));

Handling Multiple-Row Returning Subqueries

Note that a subquery will fail if more than a single row is returned. In this case the ANY and the ALL comparisons can be used to test against any item in a set of multiple rows.

ANY compares a specified value to each value returned by a subquery.

number > ANY (list of numbers)

ALL compares a value to every value returned by a subquery.

number = ALL (list of numbers)
number > ALL (subquery returning a list of numbers)
string > ALL (subquery returning a list of character strings)