Click here to Skip to main content
Click here to Skip to main content

Where Vs Having / Difference between having and Where clause

, 15 Apr 2008
Rate this:
Please Sign up or sign in to vote.
Basic difference between Sql Where and Having Clause

Introduction

We always get confused between WHERE and Having clause and make mistakes. Here in this article, I will try to highlight all the major differences between WHERE and HAVING, and things you should be aware of, when using either WHERE or HAVING.

------------------------------------------------------------------------------

Most of the time you will get the same result with Where or Having . The below given two SQL command produces the same result set That is, both count the number of records found for the states of California and Los Angles.

SELECT state, COUNT(*)
FROM Test
WHERE state IN ('CA', 'LA')
GROUP BY state
ORDER BY state

SELECT state, COUNT(*)
FROM Test
GROUP BY state
HAVING state IN ('CA', 'LA')
ORDER BY state

Background

(Optional) So, where is the difference ,Which is better? I'll let you answer those questions in a minute.

The main reason for using WHERE clause is to select rows that are to be included in the query. For example, assume table Test.Suppose I want the names, account numbers, and balance due of all customers from California and Los Angles. Since STATE is one of the fields in the record format, I can use WHERE to select those customers.

Using the code

SELECT cusnum, lstnam, init
FROM Test
WHERE state IN ('CA', 'LA')

CUSNUM LSTNAM INIT BALDUE
====== ============ ==== ========
938472 John G K 37.00
938485 Mark J A 3987.50
593029 Lily E D 25.00


Suppose I want the total amount due from customers by state. In that case, I would need to use the GROUP BY clause to build an aggregate query.

SELECT state,SUM(baldue)
FROM Test
GROUP by state
ORDER BY state

State Sum(Baldue)
===== ===========
CA 250.00
CO 58.75
GA 3987.50
MN 510.00
NY 589.50
TX 62.00
VT 439.00
WY .00

Points of Interest

Suppose I want the same information, but I don't care about states where nobody owes me any money. Since the total owed by state is an aggregate figure, i.e., the figure is generated from a group of records, you must use HAVING to select the proper data.

SELECT state,SUM(baldue)
FROM Test
GROUP by state
HAVING SUM(baldue) > 0
ORDER BY state

State Sum(Baldue)
===== ===========
CA 250.00
CO 58.75
GA 3987.50
MN 510.00
NY 589.50
TX 62.00
VT 439.00



Here's the rule. If a condition refers to an aggregate function, put that condition in the HAVING clause. Otherwise, use the WHERE clause.

Here's another rule: You can't use HAVING unless you also use GROUP BY.

Now, go back to the first example, where WHERE and HAVING produce the same result set. What's the difference? The first query uses the WHERE clause to restrict the number of rows that the computer has to sum up. But the second query sums up all the rows in the table, then uses HAVING to discard the sums it calculated for all states except Texas and Georgia. The first query is obviously the better one, because there is no need to make the computer calculate sums and then throw them away.

For complete article please visit <a href="http://gouravverma.blogspot.com/2008/04/where-vs-having-difference-between.html">gouravverma.blogspot.com</a>

History

Keep a running update of any changes or improvements you've made here.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

gouravverma

India India
No Biography provided

Comments and Discussions

 
GeneralMy vote of 4 PinmemberSuvP17-Jun-13 1:20 
GeneralMy vote of 2 PinmemberMember 95218576-Nov-12 23:06 
QuestionNot an article PinmvpRichard MacCutchan5-Jul-11 11:22 
Generalhaving clause vs where clause PinmemberMember 458490820-Apr-11 20:31 
GeneralRe: having clause vs where clause PinmvpRichard MacCutchan5-Jul-11 11:26 
GeneralJust format it a bit :) Pinmember leppie 15-Apr-08 5:57 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140821.2 | Last Updated 15 Apr 2008
Article Copyright 2008 by gouravverma
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid