A subquery is a query expression nested within another query expression. A subquery must be evaluated before the outer query that contains it. Subqueries can appear in the WHERE clauses of SELECT, INSERT, UPDATE, and DELETE statements, in the query expression of a CREATE VIEW statement, and within other subqueries. Scalar subqueries, a particular type of subquery, can appear anywhere a value expression is permitted. In most cases, a subquery is used to compare a single column to one or more values retrieved from a table or view. Consider the following example:
SELECT name, bstate FROM president
WHERE bstateab IN (SELECT ab
FROM state
WHERE pop = 2000000);This query returns president names and state of birth from the president table for Presidents whose state of birth has a population greater than 2 million. Because population figures are not kept in the president table, the comparison predicate pop=2000000 must be applied to the state table. This example shows how this can be accomplished with a subquery.
A subquery must specify exactly one column unless it appears in an EXISTS predicate, or as a nested table expression (see below). A subquery may specify an asterisk (that is, SELECT *) only if the subquery is used in an EXISTS predicate, or as a nested table expression. A subquery can contain an ORDER BY clause, but only if it also includes a FETCH FIRST...ONLY clause.
A subquery must always be enclosed in parentheses.
An operand of a join can be more complex than the name of a single table or view: a subquery can be used as a complex operand in the FROM clause. Such an operand is called a nested table expression.
Suppose we wanted to find out how many Presidents have a state of birth with a population greater than 2 million and were also licensed to practice law. Assume there is a table called barpass that keeps a record of all the Presidents who have passed the bar exam. We could rewrite the query from the previous example so that it constitutes the nested table expression in the following example:
SELECT temp.name, B.pdate
FROM barpass AS B, (SELECT P.name, P.bstate
FROM president AS P, state AS S
WHERE P.bstateab = S.ab AND
S.pop = 2000000) AS temp
WHERE temp.name = B.name AND temp.bstate = B.state;
Here, the inner query is executed first, returning a results table that is joined with the barpass table to determine the name of each President who passed the bar, along with the date that it was accomplished. Note that in the outer query, when referring to the name field, the correlation name used in the subquery (P) is superseded by the correlation name from the outer query (temp).The following restrictions apply to a nested table expression:
- It must be enclosed in parentheses.
- It must have a correlation name.
Also, unlike other types of subqueries, a nested table expression can contain the UNION operator.
A scalar subquery is a subquery that returns a single value. This type of subquery can appear wherever a value expression argument is allowed. If more than one value is returned by the subquery, an error message is displayed. If an empty set is produced by the subquery, a null value is returned. As with regular subqueries, scalar subqueries must be contained in parentheses.
The following example shows how a scalar subquery can be used to find out which employees in a company have a salary greater than the average for all employees:
SELECT e_no, salary
FROM employee
WHERE salary > (SELECT AVG(salary) FROM employee);
Here, each employee's salary is compared against the average, which is determined with a scalar subquery in the WHERE clause. If the employee's salary is above average, the employee number and salary are added to the result set. Note that the same table (employee) is queried twice in this example: first in the inner query (the scalar subquery) that calculates the average employee salary; second, in the outer query that returns information about employees with above average salaries.