-> FROM album AS a, album AS a WHERE

| 3 | 2 |
| 1 | 5 |
| 4 | 1 |
| 1 | 6 |
| 5 | 1 |
| 6 | 1 |
| 1 | 7 |
+-----------+----------+
13 rows inset sec)
But it still doesn’t work We get all 13 albums as answers. The reason is that an album still matches itself because it occurs in both aliased tables.
To get the query to work, we need to make sure an album from one aliased table doesn’t match itself in the other aliased table. The way to do so is to specify that the albums in each table shouldn’t have the same artist:
mysql> SELECT a1.artist_id, a2.album_id
-> FROM album AS a, album AS a2
-> WHERE a1.album_name = a2.album_name
-> AND a1.artist_id != a2.artist_id;
Empty set (0.00 sec)
You can now see that there aren’t two albums in the database with the same name but by different artists. The additional AND a1.artist_id != a2.artist_id stops answers from being reported where the artist is the same in both tables.
Table aliases are also useful in nested queries that use the
EXISTS
and
ON
clauses. We show you examples later in this chapter when we introduce nested techniques.
Aggregating Data
Aggregate functions allow you to discover the properties of a group of rows. You use them for purposes such as discovering how many rows there are in a table, how many rows in a table share a property (such as having the same name or date of birth, finding averages (such as the average temperature in November, or finding the maximum or minimum values of rows that meet some condition (such as finding the coldest day in
August).
This section explains the GROUP BY
and
HAVING
clauses, the two most commonly used
SQL statements for aggregation. But first, it explains the
DISTINCT
clause, which is used to report unique results for the output of a query. When neither the
DISTINCT
nor the
GROUP BY
clause is specified, the returned raw data can still be processed using the aggregate functions that we describe in this section.

Download 4.24 Mb.

Share with your friends: