HAVING clause

The HAVING clause has similar effect to the WHERE clause. The syntax is virtually the same:

SELECT <some column(s)> FROM <table> HAVING <met a certain condition>;

Indeed, in some statements, one can be tempted to replace WHERE with HAVING because the syntax is so similar, and sometimes one would not notice much, if any, difference in the returned data. However, each has its purpose and is applied differently, as discussed later in this chapter.

WHERE versus HAVING: Syntax

The HAVING clause can only be applied to columns that have been previously indicated in the statement. For example, using the sakila database, let's say we wanted every record from film that was updated since 2005. Trying to hack from our knowledge of WHERE, we might try the following:

SELECT title FROM film HAVING YEAR(last_update) > '2005';

But we would be wrong and would be greeted with an error:

ERROR 1054 (42S22): Unknown column 'last_update' in 'having clause'

One way of resolving the...