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.
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.
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE #temp_employee ( id INT, empname VARCHAR(100), age INT, empaddress VARCHAR(200) ) |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
INSERT INTO #temp_employee VALUES (1, 'XYZ', 25, 'new road ') INSERT INTO #temp_employee VALUES (2, 'ABC', 28, 'old road') INSERT INTO #temp_employee VALUES (3, 'AAB', 35, NULL) INSERT INTO #temp_employee VALUES (4, 'CBC', 25, 'sector 24') |
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
|
1 2 3 4 5 |
SELECT * FROM #temp_employee WHERE id > 1 |
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.
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.
|
1 2 3 4 5 |
SELECT * FROM #temp_employee WHERE empname LIKE '%A%' |
% 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.
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.
|
1 2 3 4 5 6 |
SELECT age, Count(*) AS no_of_employees FROM #temp_employee GROUP BY age |
The group’s output by the query returns 3 rows as shown below screenshot.
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.
|
1 2 3 4 5 6 7 |
SELECT age, Count(*) AS no_of_employees FROM #temp_employee WHERE empname LIKE '%b%' GROUP BY 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.
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.
|
1 2 3 4 5 6 7 |
SELECT age, Count(*) AS no_of_employees FROM #temp_employee GROUP BY age HAVING Count(*) > 1 |
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.
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.
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.
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
- Understanding the Scalability in Oracle Database - March 9, 2023
- Migrating Oracle Database to Azure SQL Database - February 9, 2023
- Common SQL Interview Questions and Answers - January 6, 2023