Database Reference
In-Depth Information
in the first
SELECT
statement, but not in the second one. MySQL uses the first ones it's
given for the column headings of the results set when using the
UNION
operator. It ig-
nores any field aliases in subsequent
SELECT
statements, so they're not needed. If you
don't give aliases, it uses the column names of the first SQL statement of the
UNION
.
The reason a
UNION
was somewhat necessary in the preceding example is because we're
using an aggregate function,
COUNT()
with
GROUP BY
. We can group by multiple
columns, but to get results like this which show separate counts for two specific values of
the same column, a
UNION
or some other method is necessary.
There are a few minor things to know about using a
UNION
. It's used only with
SELECT
statements. The
SELECT
statements can select columns from different tables. Duplicate
rows are combined into a single column in the results set.
You can use the
ORDER BY
clause to order the unified results. If you want to order the
results of a
SELECT
statements, independently of the unified results, you have to put that
SELECT
statement within parentheses and add an
ORDER BY
clause to it. When specify-
ing the columns in the
ORDER BY
clauses, you cannot preface column names with the
table names (e.g.,
families.scientific_name
). If using the column names would
be ambiguous, you should instead use column aliases. Let's expand our previous example
to better illustrate how to use the
ORDER BY
clause with
UNION
. Let's get a count for
each bird family within two orders:
Pelecaniformes
and
Suliformes
. Enter the following:
SELECT families.scientific_name AS 'Family',
COUNT(*) AS 'Species'
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(*)
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;
+-------------------+---------+
| Family | Species |
+-------------------+---------+
| Pelecanidae | 10 |
| Balaenicipitidae | 1 |