Learn through the super-clean Baeldung Pro experience:
>> Membership and Baeldung Pro.
No ads, dark-mode and 6 months free of IntelliJ Idea Ultimate to start with.
1. Overview
The WHERE and HAVING clauses in SQL filter data and refine query results. They both serve similar purposes. In particular, while the WHERE clause filters at the record level, the HAVING clause filters at the group of records level. Furthermore, understanding the differences between WHERE and HAVING is crucial for efficient data retrieval and manipulation.
In this tutorial, we’ll explore WHERE and HAVING clauses and their characteristics. Also, we’ll provide practical examples using the Baeldung University Student table. Finally, we’ll conclude with a comparative analysis to highlight their distinct features.
2. HAVING Clause
The HAVING clause filters data after an aggregation step in SQL. It allows the specification of conditions on groups of records. In particular, it’s useful when working with aggregate functions like SUM, COUNT, AVG, MAX, and MIN. The HAVING clause is typically used in conjunction with the GROUP BY clause to filter groups of data based on aggregate values.
For example, let’s consider the Student table in the Baeldung University database schema. Suppose we want to find students with an average GPA greater than 4.0. We achieve this with the HAVING clause:
SELECT name, graduation_date, AVG(gpa) AS average_gpa
FROM Student
GROUP BY name, graduation_date
HAVING AVG(gpa) > 4.0;
+-----------------+-----------------+-------------------+
| name | graduation_date | average_gpa |
|-----------------+-----------------+-------------------|
| Samantha Prabhu | 2024-06-15 | 4.900000095367432 |
| Sam Roberts | 2025-06-15 | 4.300000190734863 |
| Philip Mohan | 2025-06-15 | 5.0 |
| Roni Roto | 2025-06-15 | 4.440000057220459 |
| Reena Roy | 2025-06-15 | 4.150000095367432 |
| Pollards Grey | 2025-06-15 | 4.650000095367432 |
| Agatha Christi | <null> | 4.590000152587891 |
| Phellum Luis | 2024-06-15 | 4.210000038146973 |
| Rita Ora | 2024-06-15 | 4.199999809265137 |
+-----------------+-----------------+-------------------+
SELECT 9
Time: 0.012s
In this example, we first group the students by name and graduation_date using the GROUP BY clause. Then, we use the HAVING clause to filter out names with an average GPA of 4.0 or higher. The result displays student names with an average GPA greater than 4.0.
3. WHERE Clause
The WHERE clause filters data at the record level before any aggregation occurs. It allows the specification of conditions on individual records. In particular, it’s useful for selecting specific rows based on certain criteria.
For example, suppose we want to find all students with a GPA greater than 3.5. We can use the WHERE clause to filter these records:
SELECT id, name, gpa
FROM Student
WHERE gpa > 3.5;
+------+-----------------+------+
| id | name | gpa |
|------+-----------------+------|
| 1001 | John Liu | 4.0 |
| 1003 | Rita Ora | 4.2 |
| 1007 | Philip Lose | 3.8 |
| 1010 | Samantha Prabhu | 4.9 |
| 1617 | Philip Mohan | 5.0 |
| 1619 | Sam Roberts | 4.3 |
| 2001 | Reena Roy | 4.15 |
| 2009 | Pollards Grey | 4.65 |
| 2006 | Agatha Christi | 4.59 |
| 1101 | Jia Grey | 3.98 |
| 1103 | Rose Rit | 3.57 |
| 1107 | Phellum Luis | 4.21 |
| 1110 | Albert Decosta | 4.0 |
| 1710 | Roni Roto | 4.44 |
| 1721 | Vini Puh | 3.64 |
+------+-----------------+------+
SELECT 15
Time: 0.105s
In this example, the WHERE clause filters the records to include only students who have a GPA greater than 3.5.
As it’s apparent from the output, the result displays the id, name, and gpa of these students.
4. Differences
The key differences between the WHERE clause and the HAVING clause can be summarized as follows:
| Feature | HAVING | WHERE |
|---|---|---|
| Purpose | Filters groups of records after aggregation | Filters records before aggregation |
| Usage | It’s used with grouped data | It’s used with individual records |
| Execution Stage | Applied after the GROUP BY clause | Applied before the GROUP BY clause |
| Aggregate Functions | Can use aggregate functions directly | Can’t use aggregate functions directly |
5. Conclusion
In this article, we’ve explored the differences between the HAVING and WHERE clauses in SQL. While both are used to filter data, HAVING operates at the group level after aggregation, and WHERE operates at the record level before aggregation.
By understanding these differences, we can write efficient and accurate SQL queries, enhancing data management and retrieval processes.
The code backing this article is available on GitHub. Once you're logged in as a Baeldung Pro Member, start learning and coding on the project.