intro
Discover everything you need to know about the SQL HAVING clause and see how to filter grouped data in your queries.
The WHERE clause is an excellent ally for filtering data in an SQL query. However, this clause is executed before the row grouping operation performed by GROUP BY. What if you want to filter the grouped rows in the record set? This is where the SQL HAVING clause comes in!
In this guide, you will learn what the HAVING SQL clause is, how it works, and when to use it in real-world examples.
Let's dive in!
What Is HAVING in SQL?
The HAVING SQL clause is used in conjunction with the GROUP BY clause to filter the results of a query based on grouped values. HAVING was added to SQL to overcome the limitation of the WHERE clause. Specifically, this is the main SQL HAVING vs WHERE difference:
Both rely on logical operators and functions to filter records, but they are executed at two different times in the query plan. As an essential part of the SQL standard, almost all DBMS technologies support HAVING. To learn more, refer to the specific documentation pages:
HAVING SQL Clause: Syntax and First Examples
This is the syntax of HAVING in SQL within a [SELECT query](https://www.dbvis.com/thetable/crud-advanced-select-queries/):
1
SELECT column_1, column_2, ..., column_n
2
FROM table_name
3
[WHERE ...]
4
GROUP BY column_1, column_2, ...
5
HAVING having_condition
6
[ORDER BY ...];
Here, having_condition is the logical condition that must be satisfied by the grouped row to be included in the result set.
Note that the HAVING SQL clause must come after the GROUP BY clause and before the ORDER BY clause. So, there is a specific clause order to respect. Otherwise, you will get an SQL syntax error.
The way you construct the having_condition must follow some rules. Here are some guidelines for constructing conditions in the SQL HAVING clause:
Consider the sample query below with a HAVING clause for detecting duplicates:
1
SELECT name
2
FROM products
3
GROUP BY name
4
HAVING COUNT(*) > 1;
This query selects the names of all the products and then groups the resulting records by their names. After that, it filters the grouped results by checking for groups where the count of rows is greater than 1.
Now, assume you want the occurrence counter to appear in the SELECT clause. You could rewrite the query as below:
1
SELECT name, COUNT(*) as occurrences
2
FROM products
3
GROUP BY name
4
HAVING occurrences > 1;
That would produce the same result as before. Notice how the SQL expression referenced in having_condition is the alias assigned to an aggregate function in the SELECT clause.
Use Cases of the HAVING Clause in SQL
You just learned how to use HAVING in SQL. You are ready to see it in action in some popular use cases!
The sample queries in this section will be written in MySQL and executed in DbVisualizer, the database client with the highest user satisfaction in the market. Note that you can easily adapt them to any other SQL dialect and run them in your favorite client.
Filtering Aggregated Data
After grouping data with GROUP BY, you may need to filter the resulting rows based on aggregated data. For example, assume you want to retrieve the departments in your company where the average salary is above $50,000.
You can easily achieve this by using AVG() in the SQL HAVING clause of SQL, as shown below:
1
SELECT department, AVG(salary) AS avg_salary
2
FROM employees
3
GROUP BY department
4
HAVING avg_salary > 50000;
The SQL query above would produce a similar result to this one:
The resulting records of the query matches the conditions in HAVING
Great! You just saw how to use HAVING with aggregated SQL functions.
Filtering Grouped Rows Based on Multiple Conditions
After you grouped some rows with a GROUP BY clause, you may want to filter that data as you would do in WHERE. The HAVING SQL clause can be used to filter grouped rows based on multiple conditions, allowing for more complex filtering criteria.
For example, suppose we want to find which departments in your company have more than 5 employees and there is at least one employee who earns more than $60,000. Accomplish that with this query:
1
SELECT department
2
FROM employees
3
GROUP BY department
4
HAVING COUNT(*) > 5 AND MAX(salary) > 60000;
Note that a HAVING clause can contain more than one condition.
Identifying Duplicates
The HAVING clause in SQL can be used to identify rows that are duplicated or semantically identical—according to a specific condition—in a table. For instance, suppose you want to retrieve the documents that have the same name.
You can achieve that by running the SQL query outlined below:
1
SELECT name
2
FROM documents
3
GROUP BY name
4
HAVING COUNT(*) > 1;
Et voilà! You just learned how to use the SQL HAVING clause in real-world scenarios.
SQL HAVING Clause: Best Practices
Here is a list of tips and tricks you should consider when using HAVING in SQL:
Conclusion
In this guide, you understood what the SQL HAVING clause is and how it works. Now you know when to use HAVING to filter the results of your GROUP BY queries.
Filtering grouped data in SQL can produce unexpected results if you do not write the right query. Here is why you need a full-featured database client that supports you during the query definition process. The best one based on user satisfaction? DbVisualizer! This powerful database client supports many DBMS technologies, comes with advanced query optimization functionality, and can generate ERD-like schemas with a single click. Try DbVisualizer for free today!
FAQ
Is it possible to use HAVING when there is no GROUP BY clause?
No - it is not possible to use HAVING when there is no GROUP BY clause. HAVING is specifically used to filter groups of data formed by the GROUP BY clause. Without a GROUP BY clause, there are no groups to filter, so HAVING could not work as desired.
What is the difference between the HAVING vs WHERE SQL clauses?
The difference between WHERE and HAVING in SQL lies in their application. WHERE is used to filter rows before grouping, while HAVING filters groups after grouping. In other words, WHERE operates on individual rows, while HAVING operates on grouped rows. This is the HAVING vs WHERE SQL difference in a few words.
Is it possible to use COUNT in the SQL HAVING clause?
Yes, it is possible to use the COUNT() function in the HAVING SQL clause. COUNT() is commonly used in HAVING to filter groups based on the count of rows within each group. This allows for conditions to be applied to aggregated data, such as filtering groups with a count greater than a certain threshold.
What are the performance implications of the HAVING SQL clause?
The performance implications of the HAVING SQL clause vary depending on factors such as the size of the dataset, indexing, and complexity of the query. Since HAVING operates on grouped data, it usually involves more processing than the WHERE clause.
Is the HAVING clause in SQL the solution for filtering groups based on aggregate function results?
Yes, the HAVING clause in SQL is indeed the solution for filtering groups based on aggregate function results. It enables you to specify conditions on data aggregated with SQL aggregate functions after grouping has occurred using the GROUP BY clause.
