Database Reference
In-Depth Information
Unifying Results
Let's start this chapter bylooking at a simple method of unifying results from multiple
SQL statements. There may be times when you just want the unified results of two
SELECT
statements that don't interact with each other. In this situation, you can use the
UNION
operator, which merges two
SELECT
statements to form a unified results set. You
can merge many
SELECT
statements together simply by placing the
UNION
between them
in a chain. Let's look at an example.
ber of birds in the
Pelecanidae
family (i.e., Pelicans). Suppose we want to also know how
many birds are in the
Ardeidae
family (i.e., Herons). That's easy to do: we'd use a copy of
the same
SELECT
, but change the value in the
WHERE
clause. Suppose further that we
want to merge the results of the
SELECT
statement counting Pelicans with the results of a
SELECT
counting Herons. We'll do this with a
UNION
operator, so we can enter two com-
plete
SELECT
statements and unite them into one results set. Enter the following in the
mysql
client:
SELECT 'Pelecanidae' AS 'Family',
COUNT(*) AS 'Species'
FROM birds, bird_families AS families
WHERE birds.family_id = families.family_id
AND families.scientific_name = 'Pelecanidae'
UNION
SELECT 'Ardeidae',
COUNT(*)
FROM birds, bird_families AS families
WHERE birds.family_id = families.family_id
AND families.scientific_name = 'Ardeidae';
+-------------+---------+
| Family | Species |
+-------------+---------+
| Pelecanidae | 10 |
| Ardeidae | 157 |
+-------------+---------+
First notice that the column headings in the results is taken only from the first
SELECT
statement. Next notice that for the first fields in both
SELECT
statements, we didn't refer-
ence a column. Instead, we gave plain text within quotes:
'Pelecanidae'
and
'Ardeidae'
. That's an acceptable choice in MySQL and MariaDB. It works well when
you want to fill a field with text like this. Notice that we gave field aliases for the columns
