Database Reference
In-Depth Information
scenes. It will display only one entry per family becauseof the
GROUP BY
clause. Enter
the following:
SELECT family AS 'Bird Family',
COUNT(*) AS 'Number of Birds'
FROM
(SELECT families.scientific_name AS family
FROM birds
JOIN bird_families AS families USING(family_id)
WHERE families.scientific_name IN('Pelecanidae','Ardeidae')) AS
derived_1
GROUP BY family;
+-------------+-----------------+
| Bird Family | Number of Birds |
+-------------+-----------------+
| Ardeidae | 157 |
| Pelecanidae | 10 |
+-------------+-----------------+
This a much better way to form this unified results set than using a
UNION
. We could add
more bird family names tothe
WHERE
clause in the subquery to get more rows in the res-
ults set, instead of having to copy the
SELECT
statement for each family we add.
You can see in this example that a table subquery is the same as a table in the
FROM
clause. We can even give it an alias (e.g.,
derived_1
) as we can with a normal table.
The subquery returns a table of results (i.e., the bird family names). The
GROUP BY
clause tells MySQL to group the results based on the
family
field, the alias in the sub-
query for the
scientific_name
column of the
bird_families
table. We used that
same alias to select that field in the column list of the outer query. When a column in a
subquery is set to an alias, you have to use the alias; the column name becomes inaccess-
ible outside the subquery when an alias is given.
Performance Considerations with Subqueries
Performanceproblems can occur with subqueries if they are not well constructed. There
can be a performance drain when a subquery is placed withinan
IN()
operator as part of
a
WHERE
clause of the outer query. It's generally better to use instead the
=
operator,
along with
AND
for each
column
=
value
pair. For situations in which you suspect poor
performance with a subquery, try reconstructing the SQL statement with
JOIN
and com-
pare the differences between the two SQL statements usingthe
BENCHMARK()
function.
For ideas on improving subquery performance, Oracle has tips on their sitefor
Optimizing
