Database Reference
In-Depth Information
| Scopidae | 3 |
| Ardeidae | 157 |
| Threskiornithidae | 53 |
| Fregatidae | 13 |
| Sulidae | 16 |
| Phalacrocoracidae | 61 |
| Anhingidae | 8 |
+-------------------+---------+
The first five rows are are
Pelecaniformes
and the remaining rows are
Suliformes
. The
results are not in alphabetical order, but in the order of each
SELECT
statement and the
order that server found the rows for each
SELECT
statement based on the
family_id
.
If we want to order the results alphabetically by the family name, we have to use an
ORDER BY
clause, but after the unified results are generated. To do this, we'll wrap the
results set in parentheses to tell MySQL to treat it as a table. Then we'll select all of the
columns and rows of that results set and use the
ORDER BY
clause to order them based
on the family name. To avoid confusion, we'll add the name of the order to the results.
Enter the following:
SELECT * FROM
(
SELECT families.scientific_name AS 'Family',
COUNT(*) AS 'Species',
orders.scientific_name AS 'Order'
FROM birds, bird_families AS families, bird_orders AS orders
WHERE birds.family_id = families.family_id
AND families.order_id = orders.order_id
AND orders.scientific_name = 'Pelecaniformes'
GROUP BY families.family_id
UNION
SELECT families.scientific_name, COUNT(*), orders.scientific_name
FROM birds, bird_families AS families, bird_orders AS orders
WHERE birds.family_id = families.family_id
AND families.order_id = orders.order_id
AND orders.scientific_name = 'Suliformes'
GROUP BY families.family_id ) AS derived_1
ORDER BY Family;
+-------------------+---------+----------------+
| Family | Species | Order |
+-------------------+---------+----------------+
| Anhingidae | 8 | Suliformes |
| Ardeidae | 157 | Pelecaniformes |
| Balaenicipitidae | 1 | Pelecaniformes |
| Fregatidae | 13 | Suliformes |
