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.
A Database Manager That Is Modern, Fast, & Easy To Use
Tried a few tools. Beekeeper was the only one that I found that felt right. Most had a very 1990's feel to them - Allan
I built Beekeeper Studio because, like Allan, I wanted something more intuitive and modern than all the existing clunky apps I could find. My customers agree - they love using Beekeeper and they tell me every day! Give it a try, I bet you'll like it too.
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.
