Having and Where

Question

We use HAVING for groups of rows and WHERE for individual rows.
But the following is valid:

SELECT Salesperson, SUM(TotalSale)  
   FROM SALES  
   GROUP BY Salesperson  
   HAVING Salesperson <> 'Georgio';

I.e. use HAVING without an aggregate function. So couldn't we have also used WHERE Salesperson <> 'Georgio'; instead?

You can get some good insight about this here: http://www.codeproject.com/Articles/25258/Where-Vs-Having-Difference-between-having-and-Wher — Praveen Kumar Purushothaman, Mar 14 '13 at 08:15

score 3 · Answer 1 · answered Mar 14 '13 at 08:13

3

The simpliest answer would be, HAVING supports both condition (simple and aggregated condition) while WHERE cannot support for aggregated condition.

Also, HAVING is evaluated after the the records had been grouped.

answered Mar 14 '13 at 08:13

John Woo

258,903
69
498
492

I would add that he **should** have used `WHERE`, because it will perform better. – cypher Mar 14 '13 at 08:19

lc. · Answer 2 · 2013-03-14T08:33:28.487

Yes. The difference is the HAVING clause is evaluated after the aggregation, and the WHERE clause before.

This usually results in a performance increase by using WHERE, but this is not 100% always the case. For example, take the following:

create table foo(type int, data int);
create index bar on foo(id);

insert into foo values(1,3);

insert into foo values(2,3);
insert into foo values(2,3);
insert into foo values(2,3);
insert into foo values(2,3);
insert into foo values(2,3);

insert into foo values(3,3);

insert into foo values(5,3);

This query is better as a WHERE:

select type, sum(data)
from foo
where type = 5
group by type;

ID  SELECT_TYPE     TABLE   TYPE    POSSIBLE_KEYS   KEY     KEY_LEN     REF     ROWS    FILTERED    EXTRA
1   SIMPLE          foo     ref     bar             bar     5           const   1       100         Using where

select type, sum(data)
from foo
group by type
having type = 5;

ID  SELECT_TYPE     TABLE   TYPE    POSSIBLE_KEYS   KEY     KEY_LEN     REF     ROWS    FILTERED    EXTRA
1   SIMPLE          foo     index                   bar     5                   8       100

But this query is actually better-performaning as a HAVING, because the value type=2 is not selective enough:

select type, sum(data)
from foo
where type = 2
group by type;

ID  SELECT_TYPE     TABLE   TYPE    POSSIBLE_KEYS   KEY     KEY_LEN     REF     ROWS    FILTERED    EXTRA
1   SIMPLE          foo     ALL     bar                                         8       62.5        Using where

select type, sum(data)
from foo
group by type
having type = 2;

ID  SELECT_TYPE     TABLE   TYPE    POSSIBLE_KEYS   KEY     KEY_LEN     REF     ROWS    FILTERED    EXTRA
1   SIMPLE          foo     index                   bar     5                   8       100

I see.But this implies that we know before hand that `5` is more selective than `2` for the `type` field.But we don't usually know such statistical information when we write the queries, do we? — Jim, Mar 14 '13 at 08:45
@Jim No, we don't. But we might know statistically that by looking at a large number of EXPLAIN results of normal queries made by users, `HAVING` might be a better choice. But really, 1)most of the time `WHERE` is better, and 2)this kind of optimization is best only done as a last resort for an already-poorly-performing query, and not preemptively or proactively. I was merely pointing out an interesting case to say `WHERE` is not *always* better than `HAVING`. — lc., Mar 14 '13 at 08:54

score 1 · Answer 3 · answered Mar 14 '13 at 08:24

Answer is yes for your specific query where is (for the most part) better to use, performance wise

see it like this, the less data to group/aggregate the less a burden for the sql server, so. "where" gives better performance cause there is less work to do,

however in your query you use a <> / not equal this is a (hard) filter for the sql server to do, so you might end up in the having clause being faster, due to the server finds it more easy to just group everything and then remove this one example,

however i doubt that, what do the query optimizer say?

score 0 · Answer 4 · answered Mar 14 '13 at 08:13

0

You could have but you would lose the ability to SUM total sales for each sales person ;)

Better to use both WHERE and HAVING....

answered Mar 14 '13 at 08:13

JdMR

1,268
14
9

Having and Where

4 Answers4