WHERE/HAVING Clause Alternative: When to Use Them Differently (Basic 1 - 26)

The WHERE and HAVING clauses are both used to filter rows in a SQL query. However, there is an important difference between the two: the WHERE clause is applied to the individual rows before they are grouped, while the HAVING clause is applied to the groups of rows after they have been grouped.

This difference means that the WHERE clause should be used to filter out rows that should not be included in the query before they are grouped, while the HAVING clause should be used to filter out groups of rows that should not be included in the query after they have been grouped.

Here are some examples of when to use the WHERE clause and the HAVING clause:

WHERE clause:

• To filter out rows that do not meet a certain condition, such as filtering out rows where the ENAME column does not start with the letters SCO.

SQL

SELECT Ename, Hiredate, Sal
FROM Emp WHERE SUBSTR(Ename, 1, 3) = 'SCO';

• To filter out rows that are not relevant to the query, such as filtering out rows where the DEPTNO column is not equal to 10.

SQL

SELECT Ename, Hiredate, Sal
FROM Emp WHERE DEPTNO = 10;

• To filter out rows that are null, such as filtering out rows where the SAL column is null.

SQL

SELECT Ename, Hiredate, Sal
FROM Emp WHERE SAL IS NOT NULL;

HAVING clause:

• To filter out groups of rows that do not meet a certain condition, such as filtering out groups of rows where the average salary is less than $2,000.

SQL

SELECT D.Dname, AVG(E.Sal)
FROM Emp E, Dept D
WHERE E.DEPTNO = D.DEPTNO
GROUP BY D.Dname
HAVING AVG(E.Sal) > 2000;

• To filter out groups of rows that are not relevant to the query, such as filtering out groups of rows where the DNAME column is not equal to SALES.

SQL

SELECT D.Dname, AVG(E.Sal)
FROM Emp E, Dept D
WHERE E.DEPTNO = D.DEPTNO
GROUP BY D.Dname
HAVING D.Dname = 'SALES';

When to avoid using the WHERE clause:

• When you are using aggregation functions, such as AVG(), SUM(), or COUNT(), you should avoid using the WHERE clause to filter out rows, as this can lead to inaccurate results. Instead, you should use the HAVING clause to filter out groups of rows after they have been grouped.

For example, the following query will return an inaccurate result:

SQL

SELECT D.Dname, AVG(E.Sal)
FROM Emp E, Dept D
WHERE E.DEPTNO = D.DEPTNO
AND E.Sal > 5000
GROUP BY D.Dname;

This query will only include rows where the SAL column is greater than 5000, which will skew the average salary for each department. Instead, you should use the HAVING clause to filter out groups of rows after they have been grouped:

SQL

SELECT D.Dname, AVG(E.Sal)
FROM Emp E, Dept D
WHERE E.DEPTNO = D.DEPTNO
GROUP BY D.Dname
HAVING AVG(E.Sal) > 5000;

This query will return the average salary for each department, but only include departments where the average salary is greater than $5000.

Conclusion

The WHERE and HAVING clauses are both powerful tools for filtering rows in a SQL query. However, it is important to understand the difference between the two so that you can use them correctly. The WHERE clause should be used to filter out rows that should not be included in the query before they are grouped, while the HAVING clause should be used to filter out groups of rows that should not be included in the query after they have been grouped.