WHAT IS THE DIFFERENCE BETWEEN WHERE CLAUSE AND
HAVING CLAUSE?
WHERE and HAVING both filters out records based on one or more
conditions. The difference is, WHERE clause can only be applied on a static
non-aggregated column whereas we will need to use HAVING for aggregated
columns.
To understand this, consider this example.
Suppose we want to see only those departments where department ID is greater than 3. There is no aggregation operation and the condition needs to be applied on a static field. We will use WHERE clause here:
Suppose we want to see only those departments where department ID is greater than 3. There is no aggregation operation and the condition needs to be applied on a static field. We will use WHERE clause here:
SELECT * FROM DEPT WHERE ID > 3
ID
|
NAME
|
4
|
Sales
|
5
|
Logistics
|
Next, suppose we want to see only those Departments where Average salary
is greater than 80. Here the condition is associated with a non-static
aggregated information which is “average of salary”. We will need to use HAVING
clause here:
SELECT dept.name DEPARTMENT,
avg(emp.sal) AVG_SAL
FROM DEPT dept, EMPLOYEE emp
WHERE dept.id = emp.dept_id (+)
GROUP BY dept.name
HAVING AVG(emp.sal) > 80
DEPARTMENT
|
AVG_SAL
|
Engineering
|
90
|
As you see above, there is only one department (Engineering) where
average salary of employees is greater than 80.
No comments:
Post a Comment