Though it appears that both clauses do the same thing, they do it in different ways. In fact, their functions complement each other.
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.
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.
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.
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.
UPDATE dept SET dname = 'Accounting' WHERE deptno = 10; --works fine UPDATE dept SET dname = 'Accounting' Having deptno = 10; --error
Admin on Nov 09, 2019 at 12:09 am
If you like dEexams.com and would like to contribute, you can write your article here or mail your article to firstname.lastname@example.org . See your article appearing on the dEexams.com main page and help others to learn.