what is the difference between WHERE and HAVING [duplicate]

This question already has answers here: Closed 12 years ago.

Possible Duplicate:

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

i am learning sql syntax and i 开发者_如何学Gocan't understand this.

the second half of the question is a much more technical one. what is actually happening behind the scenes of the database between WHERE and HAVING? which one uses more resources? are they same algorithm just applying to different data sets?

thanks!

---

Where is in most queries and limits the records that the query cares about.

Having is used in "Group By" queries, and acts upon the grouped results.

Think of the Where clause as happening first. If there are 1000 records in the db, the where clause might make it so only 80 matter. And let's say that the Group By clause groups these 80 db records into (for example) 15 aggregate recordset rows. Without the Having clause, you would get all 15 of these aggregate rows back. The Having clause if for filtering upon these 15 aggregate rows.

Let's say you want a list of all the customers from Texas who made more than 5 orders last year.

• the Where clause to get all the orders from last year by people from Texas
• the Group By clause to group all the orders by customer (with a Count(OrderID) As OrderCount in your select clause)
• the Having clause would limit the customers listed to those having 5 or more orders.

---

where is used to filter rows directly in tables. Having is used to filter aggregated rows after they've been grouped by.

---

• WHERE filters the rows resulting of the FROM joins. Check out this example.
• HAVING filters the rows returned after aggregating with GROUP BY. Checkout this example.

---

The WHERE is used for filtration, and in ANSI-89 syntax - joining tables as well. You can not use aggregate functions (MIN, MAX, COUNT, etc) in a WHERE clause. IE:

WHERE x.col = 1    -- valid
WHERE COUNT(*) > 1 -- invalid

The HAVING clause is also used for filtration, but you can only use aggregate functions for filteration. IE:

HAVING COUNT(*) > 1 -- valid
HAVING x.col = 1    -- invalid

The HAVING clause can only be defined if a GROUP BY clause has been defined - you can not use a HAVING clause if using either DISTINCT or OVER (analytic function). The HAVING clause is always defined after the GROUP BY.

---

They are both conditional operators but HAVING relates only to an aggregate function on a GROUP BY statement.

Eg.

SELECT *
FROM users
WHERE username = 'bob'

Will return all users with the username 'bob'.

SELECT username, COUNT(*)
FROM users
GROUP BY username
HAVING COUNT(*) > 1

Will return which usernames have been used more than once.

---

I always thought of it as: HAVING is to groups what WHERE is to rows.