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.