Database Reference
In-Depth Information
The first factor of which you need to be mindful is how a subquery is contained within an
outer query, where you position it. For instance, if you construct an outer query which is
an
UPDATE
statement, you could place a subquery in the
WHERE
clause to provide a set
of values to which a column is equal (e.g., as in the first generic example). Or you might
locate a subquery in the
FROM
clause of an outer,
SELECT
statement (e.g., as in the
second generic example). These are where subqueries may be positioned. You can have
multiple subqueries within an outer query, but they will be positioned generally within the
FROM
clause or the
WHERE
clause.
The second factor is whether the results returned from a subquery are in keeping with the
expectations of the outer query. For instance, in the first generic example, the
UPDATE
clause has a
WHERE
clause that expects a single value from the subquery. If the subquery
returns several values, a row of columns, or a table of results, it will confuse MySQL and
cause an error. So you need to be sure that the subquery you construct will return the type
of values required by the outer query as you constructed it.
You'll better understand these factors as we look at examples of them. As mentioned at
the start of this section, the different types of subqueries are scalar, column, row, and table
subqueries. In the following subsections, we'll look at each of these types, along with ex-
amples ofthem.
Scalar Subqueries
The most basic subqueryis one that returns a single value, a scalar value. This type of
subquery is particularly usefulin a
WHERE
clause in conjunction with an
=
operator, or in
other instances where a single value from an expression is permitted. Let's look at simple
example of this. Let's get a list of bird families that are members of the
Galliformes
bird
order (i.e., Grouse, Partridges, Quails, and Turkeys). This can be done easily witha
JOIN
in which we join the
birds
and
bird_families
tables together based on the
or-
der_id
for
Galliformes
. We'll use instead a scalar subquery to get the
order_id
we
need. Enter this in
mysql
:
SELECT scientific_name AS Family
FROM bird_families
WHERE order_id =
(SELECT order_id
FROM bird_orders
WHERE scientific_name = 'Galliformes');
+----------------+
| Family |
+----------------+
| Megapodiidae |