Q

What is the difference between the WHERE and HAVING clause?

What is the difference between WHERE clause and HAVING clause?


The difference is that WHERE operates on individual rows, while HAVING operates on groups.

You can have WHERE without HAVING, you can have HAVING without WHERE, you can have both WHERE and HAVING, and you can have neither WHERE nor HAVING. But you can't have HAVING without grouping, even if the group consists of the entire result set.

A good example of the difference between WHERE and HAVING is given in my earlier answer Why WHERE cannot be used after GROUP BY.

Typically you will have at least one column to GROUP BY, such as:

select cust_id
     , count(distinct order_id) as orders
  from sales
 where order_date >= '2005-01-01'  
group
    by cust_id
having sum(order_amt) > 10000

This query returns the cust_id and number of orders for every customer who has order totals of $10,000 or more (the HAVING condition) for orders made this year (the WHERE condition).

An example of the entire result set as a single group is:

select count(*) as orders
  from sales
 where order_date >= '2005-01-01'

This query returns the number of orders this year. The aggregate function COUNT(*) operates on the single group consisting of all the rows that satisfy the WHERE clause. Note that it would be unusual to have a HAVING clause in this situation.

This was first published in February 2005

Dig deeper on SQL Server Backup and Recovery

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close