When writing SQL for Oracle Database, the difference between the WHERE clause and the HAVING clause is one of the most confusing concepts for beginners. Both perform “data filtering,” but the timing and targets of their application are distinctly different.
This article uses the EMP table from the SCOTT schema to explain the definitive difference between WHERE (row filtering) and HAVING (aggregation result filtering) and how to use them correctly.
Conclusion: Quick Reference of Differences
The biggest difference is whether it happens before or after “Aggregation (GROUP BY).”
| Item | WHERE Clause | HAVING Clause |
| Target | Rows | Groups |
| Timing | Before aggregation (Before GROUP BY) | After aggregation (After GROUP BY) |
| Aggregate Functions | Not Allowed (Cannot use SUM, AVG, etc.) | Mandatory (Used in conditions) |
| Role | Reducing source data (Basic Filter) | Selecting aggregation results (Analytical Filter) |
[What you will learn in this article]
- The correct execution order of WHERE and HAVING
- Causes and solutions for the
ORA-00934error - How to write performance-conscious SQL
1. Background and Basics: Understanding the Process Flow
Understanding the order in which the Oracle database interprets and executes SQL makes the difference clear.
SQL Evaluation Order
- FROM: Identify the table
- WHERE: Discard unnecessary rows [Here!]
- GROUP BY: Group and aggregate the remaining rows
- HAVING: Discard groups that do not meet conditions [Here!]
- SELECT: Display columns
2. Practice: Verification with SCOTT Schema
We will check the behavioral differences on an actual machine using the SCOTT.EMP table (Employee table).
You can import and use the SCOTT sample schema by executing the following:
$ sqlplus / as sysdba
SQL> @?/rdbms/admin/utlsampl.sql
$ sqlplus scott/tigerData Preparation
First, let’s grasp the full picture of the EMP table.
-- Check all records
SELECT DEPTNO, JOB, SAL FROM EMP ORDER BY DEPTNO;
SQL> SELECT DEPTNO, JOB, SAL FROM EMP ORDER BY DEPTNO;
DEPTNO JOB SAL
---------- --------- ----------
10 MANAGER 2450
10 PRESIDENT 5000
10 CLERK 1300
20 MANAGER 2975
20 ANALYST 3000
20 CLERK 1100
20 CLERK 800
20 ANALYST 3000
30 SALESMAN 1250
30 SALESMAN 1500
30 SALESMAN 1600
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 1250
14 rows selected.
Situation 1: WHERE Clause (Filtering Before Aggregation)
Goal: I want to know the total salary by department targeting only “Sales positions (SALESMAN)”. In this case, you must exclude non-sales positions before aggregating.
SELECT
DEPTNO,
SUM(SAL) AS TOTAL_SAL
FROM EMP
WHERE JOB = 'SALESMAN' -- Filter rows to "Salesman" only BEFORE aggregation
GROUP BY DEPTNO;
Execution Result:
SQL> SELECT
2 DEPTNO,
3 SUM(SAL) AS TOTAL_SAL
4 FROM EMP
5 WHERE JOB = 'SALESMAN'
6 GROUP BY DEPTNO;
DEPTNO TOTAL_SAL
---------- ----------
30 5600
Explanation: The WHERE clause worked first, removing data other than sales positions (such as CLERK and MANAGER) from the calculation target. As a result, only Department 30, where sales positions exist, was aggregated.
Situation 2: HAVING Clause (Filtering After Aggregation)
Goal: I want to calculate the “Total salary by department” and display only departments where the result is “9000 or more”. Since the total value is unknown until you aggregate, use HAVING.
SELECT
DEPTNO,
SUM(SAL) AS TOTAL_SAL
FROM EMP
GROUP BY DEPTNO -- First, aggregate all job types
HAVING SUM(SAL) >= 9000; -- Judge by looking at the aggregation result
Execution Result:
SQL> SELECT
2 DEPTNO,
3 SUM(SAL) AS TOTAL_SAL
4 FROM EMP
5 GROUP BY DEPTNO
6 HAVING SUM(SAL) >= 9000;
DEPTNO TOTAL_SAL
---------- ----------
30 9400
20 10875
Explanation: After calculating the total for all departments once, Department 10 (Total 8750), where TOTAL_SAL was less than 9000, was excluded from the results.
3. Advanced: Combining WHERE and HAVING
In practice, cases where both are combined appear frequently.
Goal: “I want to know departments where the average salary is 1500 or more, after excluding Managers (MANAGER).”
SELECT
DEPTNO,
COUNT(*) AS EMP_COUNT,
AVG(SAL) AS AVG_SAL
FROM EMP
WHERE JOB <> 'MANAGER' -- 1. First, exclude MANAGER (Row Filter)
GROUP BY DEPTNO -- 2. Aggregate by department
HAVING AVG(SAL) >= 1500; -- 3. Keep average 1500 or more (Group Filter)
Execution Result:
SQL> SELECT
2 DEPTNO,
3 COUNT(*) AS EMP_COUNT,
4 AVG(SAL) AS AVG_SAL
5 FROM EMP
6 WHERE JOB <> 'MANAGER'
7 GROUP BY DEPTNO
8 HAVING AVG(SAL) >= 1500;
DEPTNO EMP_COUNT AVG_SAL
---------- ---------- ----------
10 2 3150
20 4 1975
Point: If you try to do everything using only HAVING without WHERE, the salaries of MANAGERs you wanted to exclude would be included in the average calculation, preventing you from obtaining correct analysis results.
4. Troubleshooting
Common Error: ORA-00934
This is the error beginners encounter most often.
SQL resulting in error:
SELECT DEPTNO, SUM(SAL)
FROM EMP
WHERE SUM(SAL) > 10000 -- NG: Aggregate functions cannot be used in WHERE clause
GROUP BY DEPTNO;
SQL> SELECT DEPTNO, SUM(SAL)
2 FROM EMP
3 WHERE SUM(SAL) > 10000
4 GROUP BY DEPTNO;
WHERE SUM(SAL) > 10000
*
ERROR at line 3:
ORA-00934: group function is not allowed here
Cause and Solution: Since the WHERE clause is at the stage of evaluating “rows,” SUM (Total) does not exist yet. Conditions for aggregation results must be moved to HAVING.
5. Operation, Monitoring, and Security Notes
Impact on Performance You might think, “If the result is the same, does it matter which one filters it?” but there is a significant difference in performance (search speed).
- Principle: Filter with the
WHEREclause first whenever possible. - Reason:
GROUP BYis a heavy process involving data sorting and hashing. If you reduce the number of rows withWHEREbeforehand, the burden of the aggregation process becomes lighter, and memory usage is reduced.
6. FAQ: Frequently Asked Questions
Q1. Can I use the SELECT clause column alias in HAVING? A. You cannot use it in Oracle 19c or earlier. Writing HAVING Total_Sal > 1000 will result in an error (ORA-00904). You need to write HAVING SUM(SAL) > 1000. (Note: Usage of aliases is possible starting from Oracle 23ai).
Q2. Can I use only HAVING to replace WHERE? A. Grammatically, it is possible if it is a condition on a GROUP BY column, but it is not recommended. Example: HAVING DEPTNO = 10 works, but it is inefficient because it filters after aggregating all data. Write conditions that can be judged by a single row in WHERE.
Q3. Which is executed first, WHERE or HAVING? A. The WHERE clause is always first. Execution Order: FROM → WHERE → GROUP BY → HAVING → SELECT
Summary
- Difference in Target:
WHEREis for “Rows”,HAVINGis for “Aggregation Results”. - Usage Distinction: Condition judgment for aggregate functions (SUM, AVG, etc.) goes to
HAVING, others go toWHERE. - Performance: Discarding useless data early with
WHEREis the basis of SQL tuning. - Error Prevention: You cannot write
SUMinWHERE(ORA-00934).
It is easier to remember if you visualize it as cleaning the data with WHERE first, and then cooking (aggregating) it with GROUP BY and HAVING.
This article explains using Oracle Database 19c (screens and default values may differ in other versions).
[reference]
Oracle Database SQL Language Reference, 19c
コメント