Database Reference
In-Depth Information
The query wrapped around the most inner query, which is also a subquery, selects only
rows where the number of sightings is more than five. It orders the rows with newer mem-
bers first based on the date the members joined — we want the newest Russian member
reporting several Curlews and the like to be awarded a year of premium membership. This
subquery is limited to one row with one column. It's a scalar query.
The main query in the preceding example is using the single value from the scalar query
to determine which member to give one year of premium membership. If we hadn't added
the
LIMIT
to the scalar query, it would have returned more than one value — it then
wouldn't have been a scalar query. Based on the operator in the
WHERE
clause of its outer
query, MySQL would have returned an error message like this:
ERROR 1242 (ER_SUBSELECT_NO_1_ROW)
SQLSTATE = 21000
Message = "Subquery returns more than 1 row"
As with all subqueries, there's always a way to get the same results without a subquery,
using
JOIN
or some other method to bring results together in complex ways. To some ex-
tent, it's a matter of style which method you decide to use. I generally prefer subqueries,
especially when using them in applications I develop in PHP or Perl. They're easier for
me to decipher months or years later when I want to make changes to a program I'vewrit-
ten.
Column Subqueries
In the precedingsubsection, we discussed instances in which one scalar valuewas ob-
tained in a
WHERE
clause. However, there are times when you may want to match mul-
tiple values. For those situations, you will need to use the subquery in conjunction with an
operatorsuch as
IN
, which is used to specify a comma-separated list of values. Let's look
at an example of this.
In one of the examples in the previous subsection, we used a scalar subquery to get a list
of bird families for the bird order
Galliformes
. Suppose that we also want the common
name of one bird species from each family in the order; we want to randomly select a bird
name from each. To do this, we will create a subquery that will select a list of bird family
names for the order. Enter the following SQL statement:
SELECT * FROM
(SELECT common_name AS 'Bird',
families.scientific_name AS 'Family'
FROM birds
JOIN bird_families AS families USING(family_id)
JOIN bird_orders AS orders USING(order_id)
