Randheer Parmar
ScreenShot Difference between where and having clause.

The difference between WHERE and HAVING clause in SQL

December 16, 2022 by

This article aims to explain the WHERE, GROUP By, and HAVING clauses in detail. Also, we will see the difference between WHERE and HAVING with examples.

TSQL programming is the language used to query the data from the SQL server database. TSQL is derived from ANSI SQL. There are various clauses used in a TSQL statement to write a complete query.

What is the WHERE Clause?

WHERE clause is used to filter the data in a table on basis of a given condition. Below is a simple TSQL statement’s syntax with a WHERE clause.

Where Clause Syntax, TSQL Statement with where clause.

In the above TSQL statement, there are different clauses used to fetch data from a table. WHERE clause comes after doing the select statement on the table. After the clause, we have to put the condition on which we need to filter data. This condition can be a text or numeric condition.

Further, we will see different use cases for various filter conditions with an example. You can use the script to do the practice using SQL Server Management Studio (SSMS).

We will create a temporary table using the below syntax to hold sample data for various examples.

Note: Temporary tables automatically drop after the end of the session.

This temporary table has the ID, Name, and Address columns with numeric and character data types. Now we have to populate the table with some sample data. Use the below script to populate data in a table.

As now data is populated in the table, let’s see various filter conditions on this table.

Numeric filters with logical operator

Scenario 1. From the table, we need to find out if all the employees id with ID are greater than one

In the above query WHERE clause is used to filter out the data on basis of the ID column. The output will return all the values where the id is greater than 1 as shown in the screenshot below.

ScreenShot Difference between where and having clause.

Applying WHERE clause on Text values: For applying WHERE clause on text values we generally use like operator.

In the above scenario, if we want to find the employees with A in their name, we can write the below SQL query using the “Like” operator condition for the WHERE clause.

% sign in the above query will allow any letter before and after A. Below is the output for the query.

Employees with A in their names will be generated as an output.

ScreenShot Difference between where and having clause. sample table.

What is the HAVING clause?

The HAVING clause is used in SQL to filter grouped data. To understand the HAVING we need to understand the Group by Clause.

Group By Clause

It is used to group the data on basis of one or multiple columns.

For example, in the above scenario, we do have an age column that can qualify for the grouping scenario. In the sample data, we can have multiple employees who can have the same age. Employees of the same age can be part of the same group. We do have two employees with age 25 and one employee with age 35 and 28 each. In this data, we have three groups.

Group by is used for aggregate functions like sum, average, max, min, and count. We can write the below query the get the count of people in each age group.

The group’s output by the query returns 3 rows as shown below screenshot.

ScreenShot Difference between where and having clause.Query Output.

In the output we can see that for the age group 25 we have two employees; for others, we have only one employee. We can choose a grouping column and aggregate it on basis of our requirements.

GROUP BY statement can be used with WHERE clause also. We can first filter the data and then group the filtered data. Let’s see this scenario with an example.

We will filter out the employees with the letter b in their names and then group them based on their age.

In the above query first its filters the table records using the WHERE clause and then groups them on basis of age. The output of the query is shown in the below screenshot.

ScreenShot Difference between where and having clause.Query Output.

This query is returning the same 3 age groups but for the age group 25, one record is filtered out and it’s showing count as one. The one record which doesn’t contain ‘B’ in empname is filtered due to the WHERE clause and then the grouping operation is performed so it has one record for 25 instead of two.

Now we know how the grouping works, we need to apply a filter to the GROUP data. To filter data, we use a HAVING clause. The syntax for the HAVING clause is.

SELECT select_list
FROM table_name
GROUP BY group_list
HAVING conditions;

In this scenario, we will pull the age group which has more than one employee. Below is the query using the HAVING clause.

In this query HAVING is filtering the groups which are less than or equal to 1. In the output, we will be getting only groups HAVING more than one employee.

ScreenShot Difference between where and having clause.Query Output.

As we filtered the rows less than two other age groups 28,35 are filtered out and we are getting only one age group which is 25 with two employees.

WHERE and HAVING both can be used in one SQL query as both have different functionalities. We can filter all aggregates by HAVING a clause.

SQL Engine follows an execution order while executing a query. Below is the SQL query execution order. To understand WHERE and HAVING order we need to understand query execution order also.

SQL query execution order

FROM: SQL query execution begins from FROM clause.

WHERE: Its filter condition and next steps in order of execution of a query. Filters outs the rows which are not required.

GROUP BY: After applying to filter the GROUP BY operation group the remaining data. Internally it creates a table of groups.

HAVING: After applying group on remaining dataset HAVING clause filters if any filtration is required on GROUP data,

SELECT: After all the filters and grouping processing comes back to the select statement where it evaluates the UNIQUE, DISTINCT, And TOP operators if used in the SQL query.

ORDER BY: In the end, if ordered by has been used it sorts the remaining data set. It applies on basis of various conditions and is the outermost clause to execute by the SQL engine.

In terms of query performance WHERE Clause has a huge impact as it filters out overall data. HAVING groups of the entire data. It is a costly operation as the GROUP process summarizes all data and creates a new table.

Now we will see the difference between the HAVING and WHERE clause on various points.

WHERE

HAVING

It is used to filter data directly on the table.

It is used to filter the data of groups created on a table.

It is applied as a row operation.

It is applied as a column operation.

The WHERE clause pulls only the filter data based on condition.

The HAVING clause fetches all data before applying the filter condition.

You cannot use aggregate functions on the WHERE condition.

It is used on aggregate function on which group has performed.

WHERE works with SELECT, UPDATE, DELETE statement.

The HAVING works only with select statements.

WHERE doesn’t need a GROUP BY clause. It can execute with or without GROUP BY.

Using HAVING GROUP BY is compulsory.

Conclusion

As you can see both WHERE and HAVING are used in different scenarios. You can use both in a SQL query as and when required for writing the desired logic. Both are an integral part of any SQL development.

Randheer Parmar
Development, General database design, SQL commands, T-SQL

About Randheer Parmar

I am a Database Architect Having 16 years of Experience in Database Programming. I have expertise in SOL Server Database. I have Expertise in MSBI Stack and various other BI tool like QlikView, Micro Strategy, Tableau etc. Skill summary: 1. SQL Server DB Administration 2. SQL Server DB Model Development 3. SQL Server TSQL or complex query writing 4. MSBI SSIS, SSRS, and SSAS expertise 5. Various other BI tool Expertise

1,424 Views