Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Difference between WHERE and HAVING in SQL [duplicate]

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.

like image 998
CuriousKen Avatar asked Jun 15 '11 15:06

CuriousKen


People also ask

What is the difference between WHERE and HAVING in SQL?

What is the Difference between Where and Having Clause in SQL? If “Where” clause is used to filter the records from a table that is based on a specified condition, then the “Having” clause is used to filter the record from the groups based on the specified condition.

Can HAVING replace WHERE in SQL?

It is possible to replace HAVING with WHERE, but there is no benefit in doing so. Save this answer.

Is HAVING faster than WHERE SQL?

Both the statements will be having same performance as SQL Server is smart enough to parse both the same statements into a similar plan. So, it does not matter if you use WHERE or HAVING in your query.

What is the similarity between WHERE and HAVING clause?

WHERE Clause is used to filter the records from the table based on the specified condition. HAVING Clause is used to filter record from the groups based on the specified condition. 2.


4 Answers

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

In other words.

HAVING applies to groups.

WHERE applies to rows.

like image 101
sealz Avatar answered Oct 14 '22 13:10

sealz


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.

like image 43
Asaph Avatar answered Oct 14 '22 11:10

Asaph


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.

like image 28
venimus Avatar answered Oct 14 '22 11:10

venimus


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
like image 4
Bala R Avatar answered Oct 14 '22 11:10

Bala R