The Difference Between WHERE and HAVING in SQL

WHERE and HAVING are two essential clauses in SQL. Whether you are writing super-advanced queries or very simple ones, you will encounter the need to use both. You can think of WHERE and HAVING as siblings. They both serve a similar function (filtering), and they often appear together. But, just like siblings, they have distinct characteristics and unique roles. 

If you are in the process of sorting WHERE and HAVING, I suggest enrolling in our SQL Fundamentals skill track as a great and comprehensive starting point. It will teach you about the different SQL clauses, the SQL order of execution, optimizing SQL queries, and many other critical things.  

The Short Answer: WHERE vs. HAVING 

The short answer and the source of confusion for a lot of people is that WHERE works on row-level data, while HAVING operates on grouped data. Here is a guideline:

• WHERE filters rows before any grouping or aggregation happens. It applies to individual rows and cannot be used with aggregate functions.

• HAVING filters groups after the grouping and aggregation have been performed. It applies to the results of aggregate functions and is used in combination with GROUP BY.

A quick example to show the difference 

Let’s look at a quick example. If you want to follow along with your own workflow, you can download the property rentals dataset from this GitHub repository.

Now, suppose we need to return all properties in our dataset that have a rental price of less than $500. The detail level of each row in the dataset (one property per row) matches the detail level of the query condition. Therefore, we use WHERE in the following query:

SELECT *
FROM rentals
WHERE rental_price < 500ขับเคลื่อนโดย

The result would look like this. Notice that there are 118 rows that are returned.

Simple row filtering with WHERE. Image by Author.

Now, suppose we do not want to have a table of properties, but of cities that have an average rental price less than $2,700. Instead of having each row corresponding to a property, we will be grouping the rows and aggregating the rental price into an average rental price for each city. Therefore, we will use HAVING, as in this query:

SELECT city, AVG(rental_price) AS average_rent
FROM rentals
GROUP BY city
HAVING AVG(rental_price) < 2700;

And the result would be. Notice there is only one result. 

Simple group filtering with HAVING. Image by Author.

WHERE, HAVING, and SQL Order of Execution

We see that WHERE works with row-level filtering, while HAVING works on aggregate-level filtering. Understanding this difference also paves the way for us to learn about SQL’s order of clauses execution. 

WHERE is evaluated before GROUP BY, and right after FROM (and JOIN if present); it cannot deal with any grouping or aggregation. WHERE comes into play before any aggregation is done. That is why it operates only on row-level data. 

Meanwhile, HAVING comes after executing the GROUP BY clause. That means it comes into play after transforming the table and grouping it on a level that is different from the source table’s level of granularity. HAVING operates on the new, transformed version of the table. 

How to Use the WHERE Clause in SQL

Let’s take a step back and take a look at each clause on its own. We can start with the WHERE clause.

WHERE syntax and statements

WHERE can be used in three different SQL statements: SELECT, UPDATE, and DELETE.

WHERE in SELECT statements

In SELECT statements, which are the statements used to fetch data from the database, WHERE plays its direct role in row-level filtering and has its well-known place right after FROM clause, as we can see in the following query:

SELECT column1, column2… etc.
FROM table_name
WHERE condition;ขับเคลื่อนโดย

WHERE used with SELECT is the place where the WHERE clause most often gets confused with HAVING. 

WHERE in UPDATE statements

In addition, WHERE plays an important role in UPDATE statements to pinpoint the row where the data update should take place, as we can see with the following syntax:

UPDATE table_name
SET column_name1 = value1, column_name2 = value2… etc.
WHERE condition;ขับเคลื่อนโดย

WHERE in DELETE statements

WHERE is also a useful addition to DELETE statements to pinpoint the records (rows) that should be deleted, as we can see here:

DELETE FROM table_name
WHERE condition;

How to write WHERE conditions

WHERE conditions are written as a simple logical expression. It consists of three parts: the variable/operand, the condition, and the value/result. Let's take a look at the options for the WHERE conditions which include both comparison and logical operators.  

Here is one example where we use NOT along with the IN comparison operator:

SELECT *
FROM rentals
WHERE city NOT IN ('Cairo', 'Giza');

The statement will return all properties that are neither in the cities of Cairo nor Giza.

Properties outside Cairo and Giza. Image by Author.

WHERE use cases

Knowing that WHERE operates with SELECT, UPDATE and DELETE statements, we can foresee that it can be used for three use cases: row-level filtering, data retrieval, and data manipulation.

Row-level filtering

We can filter our table rows based on one condition or more. The following query filters rows to include only villa properties.

SELECT *
FROM rentals
WHERE type = ‘villa’;ขับเคลื่อนโดย

Selecting villa properties. Image by Author.

Data retrieval

WHERE can be used to retrieve a specific data point that we are looking for. This is similar to row-level filtering but more specific. Assuming we need to know the ID of the property that was available on the first of January 2022 in Cairo, we can use the following query:

SELECT property_id
FROM rentals
WHERE available_date = '2022-01-01'
AND city = 'Cairo';

Retrieving a data point. Image by Author.

Data manipulation

Finally, WHERE is a great helper to modify values and delete specific records in your database. For example, assuming we discovered that property 171 is actually not pet friendly, we can modify the pet_friendly column using WHERE in an UPDATE statement as follows:

UPDATE rentals
SET pet_friendly = false
WHERE property_id = 171;ขับเคลื่อนโดย

How to Use the HAVING Clause in SQL

Now, it’s time to turn to the HAVING clause in the same level of detail. 

HAVING syntax and statements

First off, we should know the HAVING clause can only be used in SELECT statements. Therefore, the only syntax it can have is as follows:

SELECT grouped_column, aggregate_function(aggregated_column)… etc.
FROM table_name
GROUP BY grouped_column
HAVING conditionขับเคลื่อนโดย

Note that you can add more than one grouped column and more than one aggregated column. We will see examples below.

How to write HAVING conditions

Like WHERE, HAVING conditions are written as logical expressions but with one additional component, the aggregation function. Thus, a HAVING condition consists of 1) Aggregate function, 2) variable/operand, 3) Comparison operator, and 4) value/result.

Aggregate functions with HAVING

SQL mainly has five aggregate functions, plus a sixth that is a special case. These functions are:

Let’s try one example. Here, we use the COUNT() function with GROUP BY to create a frequency table, and use the HAVING condition as a filter. Specifically, we need to know the cities that are mentioned 150 times or fewer, which in this context would have to do with the number of registrations. We can use this query:

SELECT city, COUNT(*) AS properties_count
FROM rentals
GROUP BY city
HAVING COUNT(*) <= 150;

Using COUNT() with HAVING. Image by Author.

Comparison and logical operators

HAVING accepts all comparison and logical operators that WHERE accepts. The only difference is that the suitable data type with HAVING depends foremost on the aggregate function, as we can see in the table, above.

HAVING use cases

Unlike WHERE, HAVING cannot be used in UPDATE and DELETE statements; HAVING is only used for data retrieval. Roughly speaking, this translates to two scenarios:

Group-level filtering

This is the common and normal use of HAVING. One example is returning only cities that have average rental prices below $2,700.

SELECT city, AVG(rental_price) AS avg_price
FROM rentals
GROUP BY city
HAVING AVG(rental_price) < 2700;

Single-row filtering

This is an uncommon case, but HAVING can be used to return a single row aggregation, like a metric or a KPI, only if it meets a certain condition. This can be achieved through the use of HAVING without GROUP BY. In the following example, we return the average rental price only if it is below $2,800. If the measure meets the condition, we would have a single-row result. If not, we would have an empty table.

SELECT AVG(rental_price) as avg_price
FROM rentals
HAVING AVG(rental_price) > 2800;

Combining WHERE and HAVING

WHERE and HAVING can be combined to filter tables both before and after aggregation. In the following example, we return the count of properties in each city, counting only properties that are pet friendly, and filtering to only cities that have more than 80 properties.

SELECT city, COUNT(*) AS number_properties
FROM rentals
WHERE pet_friendly = true
GROUP BY city
HAVING COUNT(*) > 80;ขับเคลื่อนโดย

Basic combination of WHERE and HAVING. Image by Author.

The Difference Between WHERE and HAVING in Performance

SELECT city, COUNT(*)
FROM rentals
GROUP BY city
HAVING rental_price < 2700;ขับเคลื่อนโดย

This query is inefficient because the rental_price condition does not depend on any aggregation — it filters individual rows. This query will first group all rentals by city, count them, and then filter the result, which is inefficient because it processes unnecessary rows. For this reason, this would have been a faster query:

SELECT city, COUNT(*)
FROM rentals
WHERE rental_price < 2700
GROUP BY city;ขับเคลื่อนโดย

WHERE and HAVING optimization tips

Based on the previous, we can see some of the good practices that can optimize our use of WHERE and HAVING clauses. This is important if you have big datasets.

1. Be Highly Selective: Filter according to the values that you need to filter to and no more. This will help reduce the number of rows in the view, and therefore enhance the efficiency of the query.

2. Be Simple: Take out any unnecessary conditions, aggregations and type casts. All these extras definitely come with a computational price.

3. Filter Early: If you are doing a group level aggregation, pre-filter the rows with WHERE to make the grouping process faster. By doing so, you would be reducing the number of rows that need to be processed in the grouping.

Comparison table

Let's summarize our thoughts into a convenient table:

Conclusion

Throughout the article, we explored the main difference between WHERE and HAVING in SQL, which is that the WHERE clause filters rows before aggregation, while the HAVING clause filters grouped data after aggregation. We also explored some of the lesser-known differences, such as the fact that WHERE can work with SELECT, UPDATE, and DELETE statements, but HAVING works only with SELECT. We also talked a bit about performance.

DataCamp’s SQL Basics Cheat Sheet provides a nice wrap-up of the WHERE and HAVING clauses, and some guidance on how to do filtering in SQL. Also, if you are getting started with SQL, you will want to have a look at the SQL Fundamentals skill track and the Associate Data Analyst in SQL career track.