A
WHERE
clause is not
the only way to constrain the results you see from a query. MySQL
provides two other common mechanisms:
HAVING
and
LIMIT
.
You will most commonly use HAVING
with the
GROUP BY
clause we just
described. Like a WHERE
clause, it defines your result set based on some set of calculations.
Unlike a WHERE
clause, it performs these
calculations after your results have been retrieved from the tables
in which they are stored. A WHERE
clause, for
example, scans the table in the database and pulls all records
matching the WHERE
clause. A
HAVING
clause, on the other hand, looks only at
rows that have been pulled from a database after they have been
extracted. The following query goes one step beyond our previous
search for the average salary of different ranks in getting the
average salaries only for ranks with an average salary greater than
$100,000.
mysql>SELECT rank, AVG(salary) FROM people
>GROUP BY rank HAVING AVG(salary) > 100000.00;
+----------+-------------+ | rank | AVG(salary) | +----------+-------------+ | General | 125000.0000 | +----------+-------------+ 1 row in set (0.04 sec)
Restricting the result set in a WHERE
clause would
make no sense. If it were to be valid SQL, it would work on the
entire table! Instead, we first want to perform the select and then
find only those groups in the result set whose average salary is
greater than $100,000. The HAVING
clause enables
us to perform that further restriction. More importantly, consider a
case with both a WHERE
clause and a
HAVING
clause:
mysql>SELECT rank, AVG(salary) FROM people
>WHERE rank <> 'Private'
>GROUP BY rank HAVING AVG(salary) > 100000.00;
+----------+-------------+ | rank | AVG(salary) | +----------+-------------+ | General | 125000.0000 | +----------+-------------+ 1 row in set (0.02 sec)
Because the HAVING
clause executes on the results
of the query, the average is calculated only for generals and
sergeants—not the excluded privates.
Sometimes an application is looking for only the first few
rows that match a query. Limiting
queries can help prevent bogging down the network with unwanted
results. MySQL enables an application to limit the number of results
through a LIMIT
clause in a query:
SELECT * FROM people ORDER BY name LIMIT 10;
To get the last 10 people from the table, you can use the
DESC
keyword. If you want people from the
middle, however, you have to get a bit trickier; you need to specify
the number of the first record you want to see (record 0 is the first
record, 1 the second) and the number of rows you want to see:
SELECT * FROM people ORDER BY name LIMIT 19, 30;
This sample displays records 20 through 49. The 19 in the
LIMIT
clause tells MySQL to start with record 19,
which is the twentieth record. The 30 then tells MySQL to return the
next 30
records.