Difference between WHERE and HAVING clauses

Though it appears that both clauses do the same thing, they do it in different ways. In fact, their functions complement each other.

• A WHERE clause is used is filter records from a result. The filter occurs before any groupings are made.
• A HAVING clause is used to filter values from a group.

The format of an SQL Statement :

SELECT
FROM
WHERE
GROUP BY
HAVING

To help keep things straight think like the order of execution of SQL statements as from top to bottom. That means the WHERE clause is first applied to the result and then, the remaining rows summarized according to the GROUP BY.

WHERE clause

The WHERE clause is used to filer rows from a results. For instance

SELECT COUNT(*) FROM emp;

returns 14 as the count, whereas, the query

SELECT COUNT(*) FROM emp WHERE sal > 1000;

Returns 12 as the count. This is because the WHERE clause filters out the 2 employees whose salary is less than or equal to 1000 from the results.

HAVING Clause

The HAVING clause is used to filter values in a GROUP BY. You can use them to filter out groups such as

SELECT empno,SUM(comm) commisssion FROM emp GROUP BY empno HAVING empno > 7500;

But their true power lies in their ability to compare and filter based on aggregate function results. For instance, you can select all employees whose total commission is more than 0.

SELECT empno,SUM(comm) commisssion FROM emp GROUP BY empno HAVING SUM(comm) > 0;

Since the WHERE clause's visibility is one row at a time, there isn't a way for it to evaluate the SUM across all Employees. The HAVING clause is evaluated after the grouping is created.

Combining the two: WHERE and HAVING

When SQL statements have both a WHERE clause and HAVING clause, keep in mind the WHERE clause is applied first, then the results grouped, and finally the groups filtered according to the HAVING clause.

1. Apart from SELECT queries, you can use WHERE clause with UPDATE and DELETE clause but HAVING clause can only be used with SELECT query. For example following query, which involve WHERE clause will work but other which uses HAVING clause will not work :

          UPDATE dept SET dname = 'Accounting' WHERE deptno = 10; --works fine
       
          UPDATE dept SET dname = 'Accounting' Having deptno = 10; --error
   

2. WHERE clause is used for filtering rows and it applies on each and every row, while HAVING clause is used to filter groups in SQL.
3. One syntax level difference between WHERE and HAVING clause is that, former is used before GROUP BY clause, while later is used after GROUP BY clause.
4. When WHERE and HAVING clause are used together in a SELECT query with aggregate function, WHERE clause is applied first on individual rows and only rows which pass the condition is included for creating groups. Once group is created, HAVING clause is used to filter groups based upon condition specified.