What is the difference between WHERE and HAVING clauses in SQL Server?

What is the difference between WHERE and HAVING clauses in SQL Server?

Asked on November 13, 2018 in SQL Server.
Add Comment


  • 3 Answer(s)

    We use WHERE clause to filter records from a result. The filter is occuring before the groupings are made.

    A HAVING clause is used to filter values from a group.

    WHERE

    • SELECT, INSERT, UPDATE and DELETE clause can be used with WHERE . For Example it works fine for “Update Mas_Employee Set Salary = 1500 WHERE Id =1”.
    • WHERE clause is used for filtering rows and it applies on each and every row.
    • WHERE clause is used before GROUP BY clause.
    • We can’t use aggregate functions in the where clause unless it is in a sub query contained in a HAVING clause.

    HAVING

    • HAVING clause can only be used with SELECT query. Means if you want perform INSERT, UPDATE and DELETE clause it will retuns an error.
    • For Example “Update Mas_Employee Set Salary = 1500 Having Id =1” Query will generate an error “Incorrect syntax near the keyword ‘HAVING’. “
    • HAVING clause is used to filter groups in SQL.
    • HAVING clause is used after GROUP BY clause.
    • We can use aggregate function in HAVING clause.
    Answered on November 13, 2018.
    Add Comment

    In this example of WHERE and HAVING clause, we have two tables Employee and Department. Employee contains details of employees e.g. id, name, age, salary and department id, while Department contains id and department name. In order to show, which employee works for which department we need to join two tables on DEPT_ID to get the the department name. Our requirement is to find how many employees are working in each department and average salary of department. In order to use WHERE clause, we will only include employees who are earning  more than 5000. Before executing our query which contains WHERE, HAVING, and GROUP BY clause, let see data from Employee and Department table:

    SELECT * FROM Employee;

    EMP_ID EMP_NAME EMP_AGE EMP_SALARY DEPT_ID
    1 Virat 23 10000 1
    2 Rohit 24 7000 2
    3 Suresh 25 8000 3
    4 Shikhar 27 6000 1
    5 Vijay 28 5000 2

    SELECT * FROM Department;

    DEPT_ID DEPT_NAME
    1 Accounting
    2 Marketing
    3 Sales

     

    SELECT d.DEPT_NAME, count(e.EMP_NAME) as NUM_EMPLOYEE, avg(e.EMP_SALARY) as AVG_SALARY FROM Employee e,

    Department d WHERE e.DEPT_ID=d.DEPT_ID AND EMP_SALARY > 5000 GROUP BY d.DEPT_NAME;

    DEPT_NAME NUM_EMPLOYEE AVG_SALARY
    Accounting 1 8000
    Marketing 1 7000
    Sales 2 8000

    From the number of employee (NUM_EMPLOYEE) column you can see that only Vijay who work for Marketing department is not included in result set because his earning 5000. This example shows that, condition in WHERE clause is used to filter rows before you aggregate them and then HAVING clause comes in picture for final filtering, which is clear from following query, now Marketing department is excluded because it doesn’t pass condition in HAVING clause i..e AVG_SALARY > 7000

    SELECT d.DEPT_NAME, count(e.EMP_NAME) as NUM_EMPLOYEE, avg(e.EMP_SALARY) as AVG_SALARY FROM Employee e,

    Department d WHERE e.DEPT_ID=d.DEPT_ID AND EMP_SALARY > 5000 GROUP BY d.DEPT_NAME HAVING AVG_SALARY > 7000;

    DEPT_NAME NUM_EMPLOYEE AVG_SALARY
    Accounting 1 8000
    Sales 2 8000

    Answered on January 14, 2019.
    Add Comment
    WHERE
    • We can use WHERE clause with SELECTINSERTUPDATE and DELETE clause. For Example it works fine for “Update Mas_Employee Set Salary = 1500 WHERE Id =1“.
    • WHERE clause is used for filtering rows and it applies on each and every row.
    • WHERE clause is used before GROUP BY clause.
    • We can’t use aggregate functions in the where clause unless it is in a sub query contained in a HAVING clause.
    HAVING
    • HAVING clause can only be used with SELECT query. Means if you want perform INSERTUPDATE and DELETE clause it will retuns an error. For Example “Update Mas_Employee Set Salary = 1500 Having Id =1″ Query will be generated error like “Incorrect syntax near the keyword ‘HAVING’. “.
    • HAVING clause is used to filter groups in SQL.
    • HAVING clause is used after GROUP BY clause.
    • We can use aggregate function in HAVING clause.
    Answered on January 14, 2019.
    Add Comment


  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.