SQL Having Clause | What is Having Clause in SQL With Example?

Summary: In this article, you will learn what is having clause in SQL with example? The HAVING clause in SQL always comes after the GROUP BY clause and before the ORDER BY clause. Let’s understand how to work Having Clause in SQL and how to use the SQL HAVING clause with syntax and examples.

What is Having Clause in SQL?

'Having clause' in SQL is used for aggregation operations along with ‘Where’, ‘group by’, and ‘order by’ condition statements. Furthermore, it is applied on a table/ database wherever there’s a necessity for filtering aggregate results and allows ‘group by’ and ‘order by’ conditions. When the ‘having clause' in SQL is used in a query, it’s expected that the resulting output data set can have more than one record from the table or database.

In another word, the HAVING clause in SQL includes one or more conditions that should be TRUE for groups of records. it’s just like the SQL WHERE clause of the GROUP BY clause. Therefore, The main difference is that the WHERE clause can’t be used with aggregate functions, whereas the HAVING clause in SQL can use aggregate functions.

Syntax:

Parameters:

• Table_Name: Name of table
• WHERE: It is optional parameter
• GROUP BY column1, column2…columnN: Groups the rows to apply aggregate function
• HAVING conditions: Aggregate function in the condition
• ORDER BY: Define the sorting order, optional

HAVING Clause Characteristics:

• This HAVING clause in SQL is used to filter or separate out grouping records.
• In the same way, the HAVING clause always comes after the GROUP BY clause & before the ORDER BY clause.
• The HAVING clause can include one or more than one condition.
• Similarly, the HAVING clause condition can only include columns that are used with the GROUP BY clause. Furthermore, to use other columns in the HAVING condition, use the aggregate functions with them.

How HAVING clause in SQL Works?

Let us understand in detail the working of the HAVING clause in SQL.
The GROUP BY clause always accompanies or conducts the HAVING clause. Therefore, the GROUP BY clause groups together the data that match a certain or specific criterion. Basically, it has three phases – split, apply, and combine.

• First of all, the split-phase divides the rows into groups.
• The apply phase applies a few aggregate functions on the groups of data.
• The combined phase produces a single result by combining the groups with the SQL aggregate function result.

Now that the groups are formed, the HAVING clause in SQL comes into the picture. The HAVING clause then filters out the groups which don’t satisfy or assure the given condition.

Thus, in the example above, we see that the table is first split into three groups based on the column Column1. The aggregate function to calculate the average of Coluumn2 values is then applied to the groups. Furthermore, this results in a single row for each group. The rows are then combined and filtered which is based on the condition in the HAVING clause.

Example of SQL Having Clause

For the demonstration purpose, Here, we will use the following Employee table in all examples here.

Table: Employee

Example #1: In the GROUP BY section, Here, we used the following query to retrieve the number of employees in each department, as given shown below.

SQL Script: GROUP BY

Output:

Now, Let’s filter the result of the above GROUP BY query, use the HAVING clause with the aggregate function, as given shown below.

SQL Script: GROUP BY

Here Notice that we used an aggregate function COUNT() in the HAVING clause because EmpID isn’t included in the GROUP BY clause. Furthermore, the above query will display the following result.
Output:

The following given below query will throw an error because the Salary column isn’t included in the GROUP BY clause and does not use an aggregate function.
Output:

FAQs Related Having Clause in SQL

1). What is the use of HAVING clause?

SQL – Having Clause. The HAVING Clause allows you to specify/identify conditions that filter which group results appear in the results. Furthermore, the WHERE clause places conditions on the selected columns, whereas the SQL HAVING clause places conditions on groups created by the GROUP BY clause statement.

2). What is difference between HAVING and WHERE clause?

A HAVING clause is such as a WHERE clause, but applies only to groups as a whole (i.e, to the rows in the result set representing groups), therefore, whereas the WHERE clause applies to individual rows. Thus a query can contain or carry both a WHERE clause and a HAVING clause.

3). What is HAVING and GROUP BY in SQL?

The HAVING clause is basically used instead of the WHERE with aggregate functions. When the GROUP BY Clause groups rows that have the identical or same values into summary rows. Furthermore, the having clause is simply used with the where clause in order to find out rows with certain or bound conditions. Thus, the having clause is always utilized after the Group By clause.

4). Why we use HAVING clause when using aggregate functions?

HAVING Clause is simply used in SQL as a conditional Clause with GROUP BY Clause. because this conditional clause returns rows where aggregate function results matched with given conditions or bounded conditions only. Furthermore, it added in the SQL because the SQL WHERE Clause can’t be combined with aggregate results, so it has a different purpose or unique purpose.

5). Can we use HAVING and WHERE together in SQL?

Yes, an SQL query or statement can contain or carry a WHERE and HAVING clause in SQL. Furthermore, you will use these together when you need to extract (or filter) rows for a group of data using a WHERE clause & apply a condition on the aggregate using the HAVING clause.