SQL questions

Discussion in 'Archived Threads 2001-2004' started by Thi Them, Mar 15, 2002.

  1. Thi Them

    Thi Them Producer

    Joined:
    Apr 20, 1999
    Messages:
    3,649
    Likes Received:
    0
    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
     
  2. Kevin P

    Kevin P Screenwriter

    Joined:
    Jan 18, 1999
    Messages:
    1,439
    Likes Received:
    0
    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:
    Code:
    Select * from customer_table where customer_state = 'NH'
    A sample query with a HAVING - returns all states with more than 10 customers:
    Code:
    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:
    Code:
    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
     
  3. Thi Them

    Thi Them Producer

    Joined:
    Apr 20, 1999
    Messages:
    3,649
    Likes Received:
    0
    Thanks Kevin, it couldn't have been more clear.

    ~T
     

Share This Page