SQL

SQL HAVING Clause: The Ultimate Guide

intro

Discover everything you need to know about the SQL HAVING clause and see how to filter grouped data in your queries.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT

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:

  • WHERE can filter individual rows before grouping and aggregation;
  • HAVING can filter grouped rows or aggregated values after the grouping and aggregation have been performed.

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/):

Copy
        
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:

  1. You can reference columns by their aliases in SELECT or original names in the table. However, these columns should be either part of the GROUP BY clause or included in aggregate functions in the SELECT clause.
  2. Conditions in the HAVING clause can involve SQL aggregate functions such as COUNT(), AVG(), SUM(), MIN(), and MAX().
  3. You can adopt comparison operators such as =, !=, >, <, >=, <= to compare values.
  4. You can utilize logical operators like AND, OR, and NOT to combine multiple conditions.

Consider the sample query below with a HAVING clause for detecting duplicates:

Copy
        
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:

Copy
        
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:

Copy
        
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
The resulting records of the query matches the conditions in HAVING

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:

Copy
        
1 SELECT department 2 FROM employees 3 GROUP BY department 4 HAVING COUNT(*) > 5 AND MAX(salary) > 60000;
Executing the SQL HAVING query in DbVisualizer
Executing the SQL HAVING query in DbVisualizer

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:

Copy
        
1 SELECT name 2 FROM documents 3 GROUP BY name 4 HAVING COUNT(*) > 1;
Getting the documents with duplicate names
Getting the documents with duplicate names

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:

  • Always ensure that the columns referenced in the HAVING clause are either part of the GROUP BY clause or used within aggregate functions to avoid syntax errors.
  • Remember that HAVING operates on grouped data, so it must come after the GROUP BY clause.
  • Be mindful of the performance implications of the clause, especially on large datasets.
  • Use aliases for columns in aggregate functions to improve the readability and maintainability of your queries.
  • Consider using SQL subqueries within HAVING for more complex filtering criteria or when you need to filter based on aggregated results from another query.
  • Add the HAVING SQL clause only when necessary, performing preliminary filtering in the more WHERE clause instead.

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.

Dbvis download link img
About the author
Antonello Zanini

Antonello is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.

The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

Counter in MySQL: Counting Rows with Ease

author Lukas Vileikis tags MySQL SQL 8 min 2024-10-03
title

SQL OFFSET: Skipping Rows in a Query

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-09-30
title

The MySQL RENAME COLUMN Statement: How, What, and Why

author Lukas Vileikis tags MySQL SQL 6 min 2024-09-26
title

SQL FETCH: Retrieving Data In Database Cursors

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2024-09-24
title

Distributing Data in a Database: A Guide to Database Sharding

author Lukas Vileikis tags MySQL OPTIMIZATION SQL 7 min 2024-09-09
title

MariaDB Docker: Server Setup Guide

author Lukas Vileikis tags MySQL SQL 5 min 2024-08-29
title

MySQL Error #1045 Explained: Everything You Need to Know

author Lukas Vileikis tags MySQL SQL 5 min 2024-08-26
title

What Is an SQL Query Builder and How Does It Work?

author Antonello Zanini tags SQL 8 min 2024-08-19
title

SQL LIMIT Clause: Complete Handbook

author Antonello Zanini tags MySQL POSTGRESQL SQL 8 min 2024-08-12
title

What Are Vector Databases?

author Lukas Vileikis tags BIG DATA SQL 6 min 2024-08-08

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗