Difference between WHERE and HAVING clause in SQL

Difference between Where and having clause in SQL

In this article, we will look at the difference between the WHERE and HAVING Clause in SQL. This is an important question asked in Interviews so we will look at each Keyword in detail with sample queries and compare their differences. At first, let us look at the description of the WHERE and HAVING Clause.

Introduction

Let’see about quick introduction for where and having clause in SQL.

Where Clause

The WHERE Clause is typically used to filter records or extract rows from a table which satisfy a given condition. It can be used with a Single Table or while using JOIN between two or more tables. It can be used with SELECT, DELETE and UPDATE Statements.

Having Clause

The HAVING Clause is used to filter records from Groups rather than a Table. The Groups satisfying the condition given in the HAVING Clause will only appear in the result set. Moreover, it can be only used with the SELECT Statement.

Working example to demonstrate difference between Where and having in SQL

Now, to differentiate between the two in practice, we consider a Table PHARMACY_PURCHASES which contains records of purchases of products in a Pharmacy as shown below:

PURCHASE_ID PRODUCT_NAME QUANTITYSALE_AMOUNT
1CROCIN1500
2PARAXIN21200
3CROCIN1500
4CETZINE2800
5CROCIN42000
6BENADRYL21400
7PARAXIN31800

Let’s define the schema to create the above table and enter the sample records:

Now, let’s assume we want to write a query to Retrieve Products Purchased in Pharmacy having their Total Sales Greater than 2000. For this, we will use the GROUP BY Clause to Group The Products with the Sum of their respective Sale Amount. To calculate Sum, we will use the SUM() Aggregate Function. Now, to get products having Sales greater than 2000, we will use the HAVING Clause with the given condition. The query will be:

Output:

We cannot write this query using the WHERE Clause as it does not work with Aggregate Functions in SQL such as COUNT, SUM, AVG, etc. If we use the WHERE Clause instead of HAVING we get Syntax Error.

So this is one Primary Difference that WHERE Clause filters rows with only a given condition and cannot be used with Aggregates whereas the HAVING Clause can be used with conditions as well as with Aggregates.

Note: The WHERE Clause can use only Single Row function like UPPER(), LOWER() etc., and not Multiple Row Functions like above.

Now, suppose we want to Retrieve Purchase Details like Total Quantity and Total Sales Amount of products PARAXIN,CROCIN and CETZINE. We can use both the WHERE and HAVING Clause to write a query that will give the records based on the condition above.

Using WHERE Clause:

Using HAVING Clause:

The Two queries will give the same output:

Let us look at how both queries are different in performance.

The query using WHERE Clause, At first filters the rows with the given PRODUCT_NAME, then groups them according to their PRODUCT_NAME and performs the aggregates for each column. So WHERE Clause filter records before Aggregate Calculation are done.

Whereas the query using HAVING Clause, At first groups the products according to their name, performs the aggregates on each column and then filters the records based on their PRODUCT_NAME.

Hence from a Performance standpoint, HAVING is slower than WHERE Clause and should be avoided when necessary.

Now, as we looked at primary difference in practice. Let us look at key differences betwwen the two.

Difference between WHERE and HAVING Clause

WHERE Clause HAVING Clause
1. The WHERE Clause is used to filter records from a table based on a given condition.1.The HAVING Clause is used to filter records from groups rather than a table, based on a given condition.
2. It cannot be used with Aggregate Functions such as SUM, COUNT, etc, and can implement only Single Row Functions such as LOWER, UPPER, etc.2. It can be used with Aggregate functions and implements in column operations.
3. From a performance perspective The WHERE Clause is faster than the HAVING Clause.3. The HAVING Clause is slower due to aggregate calculations.
4. It can be used without a GROUP BY Clause. If a query has GROUP BY then WHERE Clause should be placed before it. 4. It can only be used with the GROUP BY Clause as it filters records from groups. Moreover, it comes after the GROUP BY Clause.
5. It can be used with SELECT, UPDATE as well as DELETE commands i.e. works well with both DML and DQL commands.5. It can be used with only SELECT(DQL) statements.
6. In simple words, it acts as a Pre-Filter.6. It acts as a Post-Filter.

So, that’s all about difference between where and having clause in SQL. You can try the above explained queries with different use cases of both WHERE and HAVING Clauses and see them in detail.

Was this post helpful?

Related Posts

  • Compare date in SQL
    08 October

    How to Compare Dates in SQL

    Table of ContentsAgendaIntroduction to DATE in SQLComparing Dates in SQL with ExamplesUsing Comparison OperatorsUsing the BETWEEN ClauseComparing Dates for DATETIME Datatypes/FormatsUsing Comparison Operator with TimeStampCasting DATETIME as DATE without TimestampUsing Between Clause with DATETIMEWas this post helpful? Agenda This article will look at yet another interesting topic in SQL commonly asked in interviews – How […]

  • nvarchar vs varchar
    29 July

    nvarchar vs varchar in SQL Server

    Table of ContentsIntroductionVARCHARNVARCHARExamples to Demonstrate Difference between VARCHAR and NVARCHARVARCHAR vs NVARCHAR Comparison ChartWas this post helpful? This article will look at the difference between the data types NVARCHAR and VARCHAR in SQL Server. These data types are commonly used for storing textual data or strings for various design purposes. We will look at the […]

  • Join 3 or more table in SQL
    05 April

    Join 3 or More Tables in SQL

    Table of ContentsJoin using Parent-Child RelationshipWas this post helpful? In this article, we will have a look at an important topic in SQL, how to perform Join when there are more than two tables given . Typically, Joins or JOIN Clauses are used to combine rows or records from two or more tables on the […]

  • Find 2nd Highest Salary Employee in SQL
    22 March

    Find 2nd Highest Salary in SQL

    Learn about how to find 2nd highest salary in SQL.

Leave a Reply

Your email address will not be published.

Subscribe to our newletter

Get quality tutorials to your inbox. Subscribe now.