What is the difference between Having and Where clause

Let us understand the difference between HAVING and WHERE clause with an example. Consider the Employees table below.

Employee Table


Use the script below to create and populate the table, so you can follow along with the examples.

CREATE TABLE [dbo].[Employees1]
(
  [Id] [int] NOT NULL,
  [Name] [nvarchar](50) NULL,
  [Dept] [nvarchar](50) NULL,
  [Gender] [nvarchar](50) NULL,
  [Country] [nvarchar](50) NULL,
  [Salary] [float] NULL,
)


Data Inserttion Script:
Insert into Employees Values(1,'John','IT','Male','UK',5000)
Insert into Employees Values(2,'Mary','HR','Female','India',3000)
Insert into Employees Values(3,'Todd','IT','Male','UK',3500)
Insert into Employees Values(4,'Pam','HR','Female','India',4000)
Insert into Employees Values(5,'Tatcher','Payroll','Male','USA',2000)
Insert into Employees Values(6,'Sunil','IT','Male','USA',1400)
Insert into Employees Values(7,'Hari','Payroll','Male','UK',2500)
Insert into Employees Values(8,'Sunitha','HR','Female','India',4000)
Insert into Employees Values(9,'Sneha','IT','Female','India',3000)
Insert into Employees Values(10,'Ruby','Payroll','Male','UK',4600)

You can use HAVING clause only when you use Group By clause. The following query will give an error stating "Column 'Employees.Dept' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause."

Select * from Employees Having Dept='IT'

So to filter the rows as they are selected from the table we use WHERE clause as shown below

Select * from Employees Where Dept='IT'

If I want to select, the toal number of employees in IT department I can write the query in 2 different ways as shown below.

1. Select Dept, COUNT(*) as Total from Employees Where Dept='IT' Group By Dept
2. Select Dept, COUNT(*) as Total from Employees Group By Dept Having Dept='IT'

The first query runs faster than the second query. This is because, in the first query we only select IT department records and then perform the count operation where as in the second query we perform the count on all the Department records and then select only the IT department and its count using the HAVING clause. As the second query has more records to process than the first query, it tends to be relatively slower.

So, a WHERE clause is used in the select statement to filter the rows as they are retrieved from the database table. HAVING clause is used in the select statement in conjunction with the Group By clause, to filter the query results after they have been grouped.

If you have spotted any errors or if you can improve this answer further, please feel free to do so by submitting the form below.

14 comments:

  1. AnonymousAugust 8, 2011 at 6:57 AM

    First Query can still be improved as follows:
    1. Select 'IT' as Dept, COUNT(1) as Total from Employees Where Dept='IT'

    ReplyDelete
  2. asp.net tutorialsDecember 25, 2011 at 6:30 AM

    how to write the query to get the count of particular table columns in a database.

    ReplyDelete
    Replies
    1. tsmith16789May 18, 2012 at 11:52 AM

      hERE IS THE SOLUTION FOR YOUR QUERY IN ms sQL SERVER:

      Select COUNT(*)

      From infoschema.Column

      Where Table_Name = 'name_your_table'

      Delete
    2. tsmith16789May 18, 2012 at 11:54 AM

      Select Count(*)

      From infoColumn.Column

      Where table_name = 'name_your_table'

      Delete
    3. UnknownJuly 16, 2013 at 2:34 PM

      Sorry, when i executed the above two queries i am getting a error
      Incorrect syntax near the keyword 'Column'.

      we can use either of the below to get the column count.
      select count(*) from SYSCOLUMNS where id=(select id from SYSOBJECTS where name='table name ')

      select count(*) from SYSCOLUMNS where id= object_id('table name')

      Delete
    4. amitnpatil.blogspot.comAugust 11, 2015 at 2:21 AM

      Select COUNT(*)
      from INFORMATION_SCHEMA.COLUMNS
      where TABLE_NAME='name_your_table'

      Delete
  3. jatin naharJanuary 4, 2013 at 3:24 AM

    1. We can write having clause with only select statement but we can use where clause in any DML statement.

    ReplyDelete
  4. Sameer ThakkarSeptember 18, 2013 at 11:23 AM

    Hi All,

    One more difference is :
    We can use aggregate function in a HAVINGclause but not in WHERE clause.

    Eg: Select Dept, COUNT(*) as Total from Employees Group By Dept Having COUNT(*) > 5
    But the same is not used in WHERE clause

    Eg: Select Dept, COUNT(*) as Total from Employees Where COUNT(*) > 5 -----this will give an error

    ReplyDelete
  5. AnonymousNovember 19, 2013 at 5:21 AM

    how to get the duplicate value in table ?

    ReplyDelete
    Replies
    1. HarishNovember 18, 2018 at 9:20 AM

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

      Delete
  6. DhansJanuary 6, 2014 at 11:22 AM

    WHERE

    ------

    --> Where is used in select, insert, update, delete statements

    --> WHERE clause works on individual rows

    --> if the query has both WHERE and HAVING clauses in the statement, WHERE Clause gets applied first. Once the rows that meet the WHERE criteria are chosen, then they are grouped and finally HAVING works on those grouped rows.





    HAVING

    --------

    --> Having can only be used in select statement

    --> Having clause is used generally with GROUP BY statement and works as a filter on grouped rows, but it can be used without GROUP BY too. When GROUP BY is not used, HAVING behaves like a WHERE clause.


    ReplyDelete
  7. UnknownJuly 11, 2016 at 11:30 AM

    Is it works?
    Select id ,name,sal,from table Group by I'd,name,sal having (sal>avg(sal))

    ReplyDelete
    Replies
    1. AnonymousAugust 23, 2016 at 7:06 AM

      Sorry it won't works since the aggregate function is not applied on the column id.It is not possible to use the having clause as a conditional.

      Delete

If you are aware of any other sql server questions asked in an interview, please post them below. If you find anything missing or wrong, please feel free to correct by submitting the form below.

Subscribe to: Post Comments (Atom)
 
Disclaimer - Terms of use - Contact Us