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




Custom Search