CODEWITHSUNDEEP
sqlgroup-bywhere-clauseaggregate-functionshaving
SanHolo
SanHolo

Reputation: 25

SQL - HAVING MIN() vs WHERE

Are these queries exactly the same, or is it possible to get different results depending on the data?

SELECT A, B, C, D 
FROM Table_A 
GROUP BY A, B, C, D , E
HAVING A in (1,2) AND E = 1 AND MIN(status) = 100

SELECT A, B, C, D 
FROM Table_A 
WHERE A IN (1,2) AND E = 1 AND status = 100
GROUP BY A, B, C, D , E

Upvotes: 1

Views: 1526

Answers (3)

Barbaros Özhan
Barbaros Özhan

Reputation: 65363

They're not equal.

When you consider the following block 

create table Table_A(A int, B int, C int, D int, E int, status int);
insert into Table_A values(1,1,1,1,1,100);
insert into Table_A values(1,1,1,1,1,10);
insert into Table_A values(2,1,1,1,1,10);

SELECT A, B, C, D, 'First Query' as query 
FROM Table_A 
GROUP BY A, B, C, D , E
HAVING A in (1,2) AND E = 1 AND MIN(status) = 100;

SELECT A, B, C, D, 'Second Query' as query   
FROM Table_A 
WHERE A IN (1,2) AND E = 1 AND status = 100
GROUP BY A, B, C, D , E

you get 

A   B   C   D   query
-   -   -   -   -------------
1   1   1   1   Second Query

as a result ( only the second one returns ),

since for both of the groupings 1,1,1,1,1 and 2,1,1,1,1 -> min(status)=10.

For this reason min(status)=100 case never occurs and first query returns no result.

Rextester Demo

Upvotes: 1

Danny
Danny

Reputation: 51

At a high level:

  • The where clause specifies search conditions for the rows returned by the Query and limits rows to a meaningful set.

  • The having clause works as a filter on top of grouped rows.

Upvotes: 0

Hambone
Hambone

Reputation: 16397

A couple of things:

HAVING MIN(status) = 100

and

WHERE status = 100

are different. The where condition filters out anything that is not 100, period -- it's not even evaluated. The having clause only evaluates it after every record has been read and it looks at the result of the aggregate function (min) for the specified grouping.

Also, a more subtle difference is that the "where" clause for non-aggregate functions is preferable because it can make use of any index on the table, and equally important it will prevent records from being grouped and joined.

For example

having E = 1

and

where E = 1

functionally do the same thing. The difference is you need to collect, group and sort a bunch of records only to discard them using "having," whereas the "where" option removes them before any grouping ever occurs. Also, in this example, with the "where" option, you can remove E from the grouping criteria since it is always 1.

Upvotes: 0

Related Questions