SQL: What's the difference between HAVING and WHERE?

Question

I must be googling in the wrong way or I'm having a stupid moment in time.

What's the difference between HAVING and WHERE in an SQL SELECT statement?

EDIT: I've marked Steven's answer as the correct one as it contained the key bit of information on the link:

When GROUP BY is not used, HAVING behaves like a WHERE clause

The situation I had seen the WHERE in did not have GROUP BY and is where my confusion started. Of course, until you know this you can't specify it in the question.

Many thanks for all the answers which were very enlightening.

Answer 1

HAVING is used when you are using an aggregate such as GROUP BY.

SELECT edc_country, COUNT(*)
FROM Ed_Centers
GROUP BY edc_country
HAVING COUNT(*) > 1
ORDER BY edc_country;

Answer 2

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

Check out this w3schools link for more information

Syntax:

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

Answer 3

Searching for "difference between HAVING and WHERE" on Google has this as second hit: http://blog.sqlauthority.com/2007/07/04/sql-server-definition-comparison-and-difference-between-having-and-where-clause/

Answer 4

I use HAVING for constraining a query based on the results of an aggregate function. E.G. select * in blahblahblah group by SOMETHING having count(SOMETHING)>0

Answer 5

From here.

the SQL standard requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions

as opposed to the WHERE clause which is applied to database rows

Answer 6

HAVING is used to check conditions after the aggregation takes place.

WHERE is used before the aggregation takes place.

This code:

select City, CNT=Count(1)
From Address
Where State = 'MA'
Group By City

Gives you a count of all the cities in MA.

This code:

select City, CNT=Count(1)
From Address
Where State = 'MA'
Group By City
Having Count(1)>5

Gives you the count of all the cities in MA that occur 6 or more times.

Answer 7

WHERE is applied as a limitation on the set returned by SQL; it uses SQL's built-in set oeprations and indexes and therefore is the fastest way to filter result sets. Always use WHERE whenever possible.

HAVING is necessary for some aggregate filters. It filters the query AFTER sql has retrieved, assembled, and sorted the results. Therefore, it is much slower than WHERE and should be avoided except in those situations that require it.

SQL Server will let you get away with using HAVING even when WHERE would be much faster. Don't do it.

Answer 8

In an Aggregate query, (Any query Where an aggregate function is used) Predicates in a where clause are evaluated before the aggregated intermediate result set is generated,

Predicates in a Having clause are applied to the aggregate result set AFTER it has been generated. That's why predicate conditions on aggregate values must be placed in Having clause, not in the Where clause, and why you can use aliases defined in the Select clause in a Having Clause, but not in a Where Clause.

Answer 9

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

Answer 10

This is a great answer to the question:

http://www.programmerinterview.com/sql/clauses.php