Differences between where and having clause

WHERE: Where clause is used to filter the data based on condition. Only rows that meets conditions contribute data to the result set.
Filters rows
Where clause is executed before group by clause.
Use where clause, if there is no aggregate function in condition.

Get employees whose designation is manager
SELECT * FROM emp where job=‘manager’

Select employees whose salary is more than 5000 dollars
SELECT * FROM emp where salary>5000

HAVING: The having clause is a =n additional filter that is applied to the the result set. Logically, the HAVING clause filters rows from the intermediate result built from applying any FROM, WHERE, or GROUP BY clauses in the SELECT statement. Having clauses typically used with GROUP BY clause.
Filters groups
Where clause is executed after group by clause.
Use where clause, if there is aggregate function in condition.

Display departments information which are paying total more than 5000 dollars.
SELECT deptno, sum(salary) from emp group by deptno
having sum(salary)>5000

SQL Select statement:

SELECT select_list [ INTO new_table ]
[ FROM table_source ] [ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]

Responses are currently closed, but you can trackback from your own site.

Comments are closed.

Powered by k2schools
%d bloggers like this: