December 21, 2022 By Matthew Rathbone

In SQL, the WHERE clause is used to filter the results of a query based on specified conditions. The HAVING clause is similar, but is used instead to filter the results of a query based on specified conditions for aggregate functions.

How to use WHERE in SQL

Here is an example of how the WHERE clause might be used:

SELECT *
FROM users
WHERE age > 30;

In this example, the WHERE clause is used to filter the results of the query to only include rows from the users table where the age is greater than 30.

How to use HAVING in SQL

The HAVING clause, on the other hand, would be used in a query that includes aggregate functions, such as COUNT or SUM. Here is an example of how the HAVING clause might be used:

SELECT city, COUNT(*)
FROM users
GROUP BY city
HAVING COUNT(*) > 100;

In this example, the HAVING clause is used to filter the results of the query to only include rows where the count of users in a given city is greater than 100.

Having requires a GROUP BY

It’s important to note that the HAVING clause must be used in conjunction with a GROUP BY clause, whereas the WHERE clause can be used on its own. The GROUP BY clause is used to group the results of a query by one or more columns, and is often used in conjunction with aggregate functions.

Using WHERE and HAVING together

You can use both WHERE and HAVING in the same query without any prolems.

Here we count the number of users over 30 who live in each city, only where the city has more than 100 users.

SELECT city, COUNT(*)
FROM users
WHERE age > 30
GROUP BY city
HAVING COUNT(*) > 100;

WHERE vs HAVING Summary

To summarize, the WHERE clause is used to filter the results of a query based on specified conditions, whereas the HAVING clause is used to filter the results of a query based on specified conditions for aggregate functions. The HAVING clause must be used in conjunction with a GROUP BY clause, whereas the WHERE clause can be used on its own.

Beekeeper Studio Is A Free & Open Source Database GUI

Best SQL query & editor tool I have ever used. It provides everything I need to manage my database. - ⭐⭐⭐⭐⭐ Mit

Beekeeper Studio is fast, intuitive, and easy to use. Beekeeper supports loads of databases, and works great on Windows, Mac and Linux.

Beekeeper's Linux version is 100% full-featured, no cut corners, no feature compromises.