Jan 14, 2015

Difference between a HAVING CLAUSE and a WHERE CLAUSE

Only with the SELECT statement we can be use HAVING Clause.  Basically HAVING Clause is used with GROUP BY Clause. Without used of GROUP BY clause, HAVING behaves like a WHERE clause.

1-   WHERE clause applies to individual rows.

2-  The WHERE clause is applied first to the individual rows in the tables, means WHERE clause is fetched data from memory according to condition.

3-  We can use WHERE clause with the HAVING clause, but it apply before the GROUP BY clause.     
1-   HAVING clause is like a WHERE clause, but applies only to groups as a whole.
2-  The HAVING clause is applied to the rows in the result set, means first data is fetched from memory to result set then apply HAVING clause on the row of result set.

3-  We can apply a HAVING clause only to columns that also appear in the GROUP BY clause or in an aggregate function

Where Clause Example:

SELECT empName,empDept,empSalary WHERE empSalary=20000

HAVING Clause Example:

SELECT empName,AVG(empSalary)

GROUP BY empName,empSalary HAVING AVG(empSalary)>20000