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 |
Search WWH ::




Custom Search