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.
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
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.
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.
I hope so far you are clear about this. To understand this even better let us write two simple SQL Queries.
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.
Here in Having clause first, it will apply a sum on Sales Amount and then filter the products using Having Clause.
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.
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.