SQL questions (1 Viewer)

[teapot2001]

I've been learning SQL at sqlcourse.com. Can someone tell me the difference between the "HAVING" and "WHERE" clauses? Wouldn't using either return the same results?

Also, I'd like to learn more than that site provides. Any other website recommendations? Books?

~T

 

[Kevin P]

The WHERE clause restricts rows returned by a query based on the criteria specified.
The HAVING clause is only used with aggregate queries (those with a GROUP BY clause). It's used to restrict on the aggregate value (sum, count, average, min, max etc.) rather than the original values in the table(s).
A sample query with a WHERE - returns all customers with a state of NH:

Select * from customer_table where customer_state = 'NH'

A sample query with a HAVING - returns all states with more than 10 customers:

Select customer_state, count(*) from customer_table
group by customer_state
having count(*) > 10

You can have both a WHERE and a HAVING in the same query - the WHERE would be used to restrict based on the actual data in the table; the HAVING would be used to restrict on the summary information created by the query. For example, this query would return any state that has more than 10 delinquent customers. The WHERE filters out only the delinquent customers, then these are counted and grouped by state, and then the result set is filtered by the counts in the HAVING clause:

Select customer_state, count(*) from customer_table
where delinquent_flag = 'Y'
group by customer_state
having count(*) > 10

Hopefully this is clear as mud...
KJP

 

[teapot2001]

Thanks Kevin, it couldn't have been more clear.

~T