Having vs Where in SQL

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.