Difference between WHERE and HAVING in SQL [duplicate]

Question

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.

Drily

Answer 1

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

In other words.

HAVING applies to groups.

WHERE applies to rows.

Tailback

Answer 2

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.

Ringent

I am looking for an answer like this: one which definitively describes the scope of HAVING. The keyword in this answer is "only." That said, I'm certain I've seen a counter example to this posted on another forum, which left me confused. I will see if I can find that posted query. – Drily

@CuriousKen: Correct. The word "only" is carefully chosen. I can think of no valid counter-example which would cause me to change the wording of my answer. – Ringent

Here we go... and I think this answers my own question: This works SELECT last_name AS l FROM my_contacts HAVING l LIKE '%son'; but the following does not: SELECT last_name AS l FROM my_contacts WHERE l LIKE '%son'; note the alias. – Drily

@CuriousKen: Very interesting. You taught me something today. I've confirmed your example with a test in MySQL which I have included in my answer. – Ringent

Re your update: it actually only works if it can be disambiguated, and only on some engines. – Respecting

In that case might as well fix the syntax error in your first query, too. Where goes before group by. :-) – Respecting

@Denis: Whoops! Don't know how I missed that. Thanks. Fixed. That's what I get for not testing. Good thing I tested the rest of the queries... – Ringent

Answer 3

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.

Ostler

Order by is actually applied after having. But voting up nonetheless, with the assumption you'll edit the answer. – Respecting

Answer 4

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

Headword