O'Reilly logo

Managing & Using MySQL, 2nd Edition by Hugh E. Williams, Randy Yarger, George Reese, Tim King

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Limiting Results

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required