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. First Query can still be improved as follows:
    1. Select 'IT' as Dept, COUNT(1) as Total from Employees Where Dept='IT'

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

    ReplyDelete
    Replies
    1. hERE IS THE SOLUTION FOR YOUR QUERY IN ms sQL SERVER:

      Select COUNT(*)

      From infoschema.Column

      Where Table_Name = 'name_your_table'

      Delete
    2. Select Count(*)

      From infoColumn.Column

      Where table_name = 'name_your_table'

      Delete
    3. 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. Select COUNT(*)
      from INFORMATION_SCHEMA.COLUMNS
      where TABLE_NAME='name_your_table'

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

    ReplyDelete
  4. 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. how to get the duplicate value in table ?

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

      Delete
  6. 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. Is it works?
    Select id ,name,sal,from table Group by I'd,name,sal having (sal>avg(sal))

    ReplyDelete
    Replies
    1. 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.

 
Disclaimer - Terms of use - Contact Us