In SQL, you use the HAVING
keyword right after GROUP BY
to query the database based on a specified condition. Like other keywords, it returns the data that meet the condition and filters out the rest.
The HAVING
keyword was introduced because the WHERE
clause fails when used with aggregate functions. So, you have to use the HAVING
clause with aggregate functions instead of WHERE
.
With the HAVING
clause, you can arrange the data in your database into many groups when you use it with the GROUP BY
keyword. So, you can use it in a large database.
How to Use the HAVING
Keyword.
Suppose I have a table named students
in a student_scores
database.
SELECT * FROM students
returns the following:
You can get only the names and scores by running SELECT name, score
FROM students
.
You can then use the HAVING
keyword to filter out some students based on a condition. For example, those who have a score greater than 70.
But before that, you must use the GROUP BY
clause like this:
GROUP BY name, score
This won’t return anything yet. So you need to bring in the HAVING
keyword:
HAVING score > 70
Now, I’m able to get students who scored higher than 70:
The full query looks like this:
SELECT name, score
FROM students
GROUP BY name, score
HAVING score > 70
I’m also able to get the students who scored more than 90 this way:
SELECT name, score
FROM students
GROUP BY name, score
HAVING score > 90
The table also has an age column, so I can get the students who are over 14 years this way:
SELECT name, age
FROM students
GROUP BY name, age
HAVING age > 14
An error occurs if you use WHERE
with an aggregate function
SELECT name, count(*)
FROM students
GROUP BY name
WHERE COUNT(*) > 0
The error goes away if you use HAVING
:
SELECT name, count(*)
FROM students
GROUP BY name
HAVING COUNT(*) > 0
You can use any operator you want!
The operator is not exclusive to comparisons. So, I’m able to get students who are 13 years by changing the HAVING statement to HAVING age = 13
:
I got the students who scored 90 this way:
SELECT name, score
FROM students
GROUP BY name, score
HAVING score = 90
If the condition in the HAVING statement is not met, no row will be returned:
SELECT name, score
FROM students
GROUP BY name, score
HAVING score = 100
An Error Occurs if you Use HAVING
without GROUP BY
SELECT COUNT(*)
FROM students
HAVING score > 80
In this case, you have to use the WHERE
clause:
SELECT COUNT(*)
FROM students
WHERE score > 80
Wrapping Up
In this article, you learned how to query databases using the HAVING
keyword.
Remember that you have to use the HAVING
clause with GROUP BY
so you can get the desired data, just as you’ve seen in this article.
In situations where you can’t use the HAVING
clause, you probably need to use WHERE
.
Thank you for reading.