What Is SQL HAVING CLAUSE and How Does It Work? | SQL Queries

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.

Can we use having and where clause together?

Yes. We can use having and where clause together. Where clause filter the rows based on specific condition and the condition is not mandatory.

When you want to apply a condition on grouped records you use having clause.

Just observe the below query.

Select Product Name, Sum(Sales Amount) where Product Name = ‘ Mobile’ Group By Product Name Having (Sales Amount)>1000

Here we wanted to apply a condition on grouped column Sales Amount where the product is Mobile. Hence we have used where clause and group by together.

Similarly we can use SQL having for multiple conditions.

SQL having without group by also possible when you have only one aggregated column in your SQL select query.

I hope you understand how to use group by and having clause in sql.

Joins in SQL? using Having and Where

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. It is quite common that we use where and having clauses in SQL Joins as well.

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

1 thought on “What Is SQL HAVING CLAUSE and How Does It Work? | SQL Queries”

  1. Howdy! Someone in my Facebook group shared this website with us so I came to check it out.

    I’m definitely enjoying the information. I’m book-marking and will
    be tweeting this to my followers! Terrific blog and fantastic design and style.

Comments are closed.