Difference between WHERE and HAVING in SQL [duplicate]

This question already has answers here: 开发者_高级运维 Closed 11 years ago.

Possible Duplicate:

SQL: What's the difference between HAVING and WHERE?

I have seen various discussions on WHERE and HAVING. I still have a question: is HAVING used only when considering aggregates, or can it be used in more general terms: whenever you have created or aliased a field in your query? I know that WHERE should always be used when possible.

---

HAVING specifies a search for something used in the SELECT statement.

In other words.

HAVING applies to groups.

WHERE applies to rows.

---

HAVING is only for conditions involving aggregates used in conjunction with the GROUP BY clause. eg. COUNT, SUM, AVG, MAX, MIN. WHERE is for any non-aggregage conditions. They can even be used together in the same query. eg.

SELECT t1.id, COUNT(*) FROM table1 AS t1
    INNER JOIN table2 AS t2 ON t2.t1_id = t1.id
    WHERE t1.score > 50
    GROUP BY t1.id HAVING COUNT(*) > 2;

Update #1:

Turns out there is a non-aggregate usage of HAVING that I didn't know about. The query below which uses an alias only works with the HAVING keyword, not the WHERE keyword. See my test in MySQL:

mysql> create table my_contacts (
    -> id int unsigned primary key auto_increment,
    -> first_name varchar(32) not null,
    -> last_name varchar(32) not null,
    -> index (last_name, first_name)
    -> ) engine=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into my_contacts (first_name, last_name)
    -> values ('Randy', 'Jackson'), ('Billy', 'Johnson'), ('Sam', 'Harris'), ('Lenny', 'Benson'), ('Sue', 'Flax');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT last_name AS l FROM my_contacts HAVING l LIKE '%son';
+---------+
| l       |
+---------+
| Benson  |
| Jackson |
| Johnson |
+---------+
3 rows in set (0.00 sec)

mysql> SELECT last_name AS l FROM my_contacts WHERE l LIKE '%son';
ERROR 1054 (42S22): Unknown column 'l' in 'where clause'

Update #2:

I've now tested the novel use of HAVING on SQL Server and it does not work. So this may be a MySQL-only feature. Also, @Denis pointed out in the comments that this trick only works if the column/alias can be disambiguated and it only works on some engines.

---

The WHERE clause is used to restrict records, and is also used by the query optimizer to determine which indexes and tables to use. HAVING is a "filter" on the final resultset, and is applied after GROUP BY, so sql cannot use it to optimize the query.

WHERE is applied for each row while extracting. HAVING extracts all rows then filter the result.

Thus WHERE cannot be used for aggregate functions, because they require the full rowset to be extracted.

---

HAVING is used when you have a GROUP BY clause and you are trying to filter based on one of the grouping fields; WHERE is used for filtering otherwise.

eg.

select StudentName from students where Age > 20

select className, count(studentId) from classes group by className 
                                      having count(studentId) > 10