What Is SQL HAVING CLAUSE and How Does It Work?| The Complete Guide

In this article, we are going to discuss SQL Having Clause in SQL Queries for various scenarios. By reading this article you will be able to know everything about SQL Having Clause.

The SQL Having clause is used to specify a condition on aggregate functions and it is used along with the group by clause in SQL Queries.

For our better understanding let us see the position of Having Clause in below diagram when a series of clauses used in one single query.

SQL Having

In general, it applied to the summarized rows along with the group by clause and It is only used after the group by clause.

In this clause, the complete data first paste in the memory system and then separate according to the condition.  

Let us see a sample query using SQL having clause

In this example, I want to find out duplicate records in customers table using SQL having clause.

Let us see how we can do that.

I am using SQL editor to fire a query against sample sales DB which I have

SQL having clause sample query and DB

Above are the list of columns I have in the customer table. Here I am going to write a query to find out duplicate records I have within this table. Let us see the query below.

No duplicate records query using having

You can see here the query has given output as No results. It means there is no duplicate records in this table.

To understand this even better when you have duplicate records in your table you can use the above query and modify the table columns and table name as per your requirement and execute the query in your database.

The difference between SQL Having and Where

The major difference between having and where clause is where clause cannot be used with aggregates whereas having clauses can be used with aggregates.

Where clause filter the rows before aggregation calculations performed whereas having clause filter the rows after aggregate calculations performed.

SQL Having Sample

I hope so far you are clear about this. To understand this even better let us write two simple SQL Queries.

Query 1

What happens in the where clause here, first it will filter the records for Product iPhone and Speakers and then apply the Sum of Sales Amount.

Where Clause Table

Query 2

Here in Having clause first, it will apply a sum on Sales Amount and then filter the products using Having Clause.

SQL Having Table

How to use SQL Having Clause without using Group By?

We can also use this clause without a group by clause. Let us discuss this.

In the below example the group by clause is ignored, which makes the aggregate function calculate a value for the entire table. It excludes non-matching rows from the result set.

SQL Having Clause without Group by

How to use Having Clause in Sub Queries?

The use of this clause is exactly the same as how we use it in basic queries.

In the above query, we have used this clause to compare with subquery AVG quantity results. Similarly, you can also practice this for various scenarios.

Joins in SQL?

We can use joins to retrieve the data from two are more tables. We join the tables based on the cardinality along with primary key and foreign key relationships.

Also, you check this very important topic SQL Joins. One should learn this topic while preparing for an interview.

Leave a comment