Database Reference
In-Depth Information
WHERE common_name != ''
AND families.scientific_name IN
(SELECT DISTINCT families.scientific_name AS 'Family'
FROM bird_families AS families
JOIN bird_orders AS orders USING(order_id)
WHERE orders.scientific_name = 'Galliformes'
ORDER BY Family)
ORDER BY RAND()) AS derived_1
GROUP BY (Family);
+------------------------+----------------+
| Bird | Family |
+------------------------+----------------+
| White-crested Guan | Cracidae |
| Forsten's Scrubfowl | Megapodiidae |
| Helmeted Guineafowl | Numididae |
| Mountain Quail | Odontophoridae |
| Gray-striped Francolin | Phasianidae |
+------------------------+----------------+
In this example, we have two subqueries, a subquery within a subquery, within an outer
query. The most inner subquery is known as a nested subquery. The subqueries here are
executed before the outer query, so the results will be available before the
WHERE
clause
of the outer query is executed. In that vein, the nested subquery will be executed before
the subquery in which it is contained. In this example, the nested query is contained with-
in the parentheses of the
IN
operator — the most indented query. That SQL statement se-
lects the bird family name where the name of the order is
Galliformes
. The
DISTINCT
flagby the alias
Family
instructs MySQL to return only one entry for each distinct fam-
ily name. If we had manually entered that information, it would look like this:
('Cracid-
ae','Megapodiidae','Numididae','Odontophoridae','Phasianidae')
. This subquery is a
multiple-field or column subquery.
The inner subquery in the preceding example is a table subquery. It selects a list of all
birds that are in the list of bird families provided by its subquery. We could just select one
bird for each family at this level using a
GROUP BY
clauseto group by the
Family
name to get one bird species per family. But that would select the first rows found and the
results would be the same every time. We want to select randomly each time this SQL
statement is executed. To do that, we're selecting all of the birds for each bird family and
thenusing
ORDER BY RAND()
to randomly order the rows of the results table. Then
we're wrapping that in another query, the outer query to
GROUP BY
the bird family. That
will give us one entry for eachbird family.
