Subqueries

Subqueries
In this section we examine the use of a complete SELECT statement embedded within
another SELECT statement. The results of this inner SELECT statement (or subselect)
are used in the outer statement to help determine the contents of the final result. A subselect
can be used in the WHERE and HAVING clauses of an outer SELECT statement,
where it is called a subquery or nested query. Subselects may also appear in INSERT,
UPDATE, and DELETE statements (see Section 5.3.10). There are three types of
subquery:
n A scalar subquery returns a single column and a single row; that is, a single value. In
principle, a scalar subquery can be used whenever a single value is needed. Example
5.19 uses a scalar subquery.
n A row subquery returns multiple columns, but again only a single row. A row subquery
can be used whenever a row value constructor is needed, typically in predicates.
n A table subquery returns one or more columns and multiple rows. A table subquery can
be used whenever a table is needed, for example, as an operand for the IN predicate.
Example 5.19 Using a subquery with equality
List the staff who work in the branch at ‘163 Main St’.
SELECT staffNo, fName, lName, position
FROM Staff
WHERE branchNo = (SELECT branchNo
FROM Branch
WHERE street = ‘163 Main St’);
The inner SELECT statement (SELECT branchNo FROM Branch . . . ) finds the branch
number that corresponds to the branch with street name ‘163 Main St’ (there will be only
one such branch number, so this is an example of a scalar subquery). Having obtained this
branch number, the outer SELECT statement then retrieves the details of all staff who
work at this branch. In other words, the inner SELECT returns a result table containing a
single value ‘B003’, corresponding to the branch at ‘163 Main St’, and the outer SELECT
becomes:
SELECT staffNo, fName, lName, position
FROM Staff
WHERE branchNo = ‘B003’;
The result table is shown in Table 5.19.
We can think of the subquery as producing a temporary table with results that can be
accessed and used by the outer statement. A subquery can be used immediately following
a relational operator (=, <, >, <=, > =, < >) in a WHERE clause, or a HAVING clause. The
subquery itself is always enclosed in parentheses.
Example 5.20 Using a subquery with an aggregate function
List all staff whose salary is greater than the average salary, and show by how much
their salary is greater than the average.
SELECT staffNo, fName, lName, position,
salary – (SELECT AVG(salary) FROM Staff) AS salDiff
FROM Staff
WHERE salary > (SELECT AVG(salary) FROM Staff);
First, note that we cannot write ‘WHERE salary > AVG(salary)’ because aggregate functions
cannot be used in the WHERE clause. Instead, we use a subquery to find the average
salary, and then use the outer SELECT statement to find those staff with a salary greater
than this average. In other words, the subquery returns the average salary as £17,000. Note
also the use of the scalar subquery in the SELECT list, to determine the difference from
the average salary. The outer query is reduced then to:
SELECT staffNo, fName, lName, position, salary – 17000 AS salDiff
FROM Staff
WHERE salary > 17000;
The following rules apply to subqueries:
(1) The ORDER BY clause may not be used in a subquery (although it may be used in the
outermost SELECT statement).
(2) The subquery SELECT list must consist of a single column name or expression,
except for subqueries that use the keyword EXISTS (see Section 5.3.8).
(3) By default, column names in a subquery refer to the table name in the FROM clause
of the subquery. It is possible to refer to a table in a FROM clause of an outer query
by qualifying the column name (see below).
(4) When a subquery is one of the two operands involved in a comparison, the subquery
must appear on the right-hand side of the comparison. For example, it would be incorrect
to express the last example as:
SELECT staffNo, fName, lName, position, salary
FROM Staff
WHERE (SELECT AVG(salary) FROM Staff) < salary;
because the subquery appears on the left-hand side of the comparison with salary.
Example 5.21 Nested subqueries: use of IN
List the properties that are handled by staff who work in the branch at ‘163 Main St’.
SELECT propertyNo, street, city, postcode, type, rooms, rent
FROM PropertyForRent
WHERE staffNo IN (SELECT staffNo
FROM Staff
WHERE branchNo = (SELECT branchNo
FROM Branch
WHERE street = ‘163 Main St’));
Working from the innermost query outwards, the first query selects the number of the
branch at ‘163 Main St’. The second query then selects those staff who work at this
branch number. In this case, there may be more than one such row found, and so we
cannot use the equality condition (=) in the outermost query. Instead, we use the IN
keyword. The outermost query then retrieves the details of the properties that are managed
by each member of staff identified in the middle query. The result table is shown in
Table 5.21.