SQL: using WHERE AND instead of HAVING

Question

here's an example of a SQL statement where we use HAVING:

select column1 from table1
where condition1
having condition2;

isn't it the same exact thing if we do this:

select column1 from table1
where condition1 AND condition2;

what is the difference between these two?

Answer 1

Hi,

No, because having is for aggregate functions or group by clause.

For example:

SELECT COUNT(ID)
FROM tablexpto
where name = 'a'
having count(ID) > 1

The first query would not run.

Answer 2

HAVING is for use with aggregates, e.g., HAVING SUM(column1) > 200, WHERE is just for the columns, e.g., WHERE column1 < 20.

Answer 3

Having only works with a group by clause and limits records after they are grouped.

Answer 4

In your example, they should do the same thing. But WHERE gets processed before any GROUP BY, and so it doesn't have access to aggregated values (that is, the results of Min(), Max(), etc. functions). HAVING gets processed after GROUP BY and so can be used to constrain the result set to only those with aggregated values that match a certain predicate.

Answer 5

No, they are completely different.

Having conditions are for grouping aggregate functions. They are computed after the aggregated value was computed.

Example:

select id, count(1) 
  from table
 where COND1
 having count(1) > 1

Here, the having part is evaluated after the query computed the count(1) value for each group.

Answer 6

In your example, it is the same because you have no GROUP BY

Otherwise, HAVING is applied after GROUP BY which is applied after WHERE...

Saying that, HAVING with a simple filter (x = 2) is exactly the same as WHERE because x = 2 only has meaning if you grouped on it. You normally use HAVING on an aggregate (COUNT(*) > 2 for example) that can only be applied after GROUP BY

Answer 7

HAVING specifies a search condition for a group or an aggregate function used in a SELECT statement.

A HAVING clause is like a WHERE clause, but applies only to groups as a whole, whereas the WHERE clause applies to individual rows.

Having http://blog.sqlauthority.com/2007/07/04/sql-server-definition-comparison-and-difference-between-having-and-where-clause/

Answer 8

to use having you need a group by clause. you will get an error without one

Answer 9

As others have (mostly) correctly stated, in SQL the WHERE clause is evaluated before the SELECT clause, therefore the result of a set function is 'out of scope' in the WHERE clause.

For example, you CANNOT do this:

SELECT Subject, MAX(Mark) AS TopScore
  FROM Exam_Marks
 GROUP 
    BY Subject
 WHERE TopScore <= 70;

because the column correlation name TopScore is not in scope for the WHERE clause.

Of course we could use a subquery:

SELECT DT1.TopScore
  FROM (
        SELECT Subject, MAX(Mark) AS TopScore
          FROM Exam_Marks
         GROUP 
            BY Subject
       ) AS DT1
 WHERE DT1.TopScore <= 70;

The problem was, early implementations of SQL (starting with IBM's System R) lacked support for derived tables, hence the unintuitive HAVING was born.

You can read the whole sorry story in HAVING A Blunderful Time (or Wish You Were WHERE) by Hugh Darwen, from which I've borrowed the above examples.