Lately, I've been working with postgreSQL, and one of the concepts that have been really helpful but at the same time I found it difficult was the difference between WHERE and HAVING. So I wanted to give a little context and show with an example.
The WHERE clause applies the condition to individual rows before the rows are summarized into groups by the GROUP BY clause. However, the HAVING clause applies the condition to the groups after the rows are grouped into groups. That's why the HAVING clause is applied after whereas the WHERE clause is applied before the GROUP BY clause.
The example
Let's suppose that a school offers courses in which students can enrol, the school considers a star student to those that at in the final grade achieve a score superior of 8, let's see the table.
If the directives want to see who are the star students in general we use a query like this
select *
from inscription
where final_grade > 8
But if the directives would like to see in which groups are more than one star student they will need to use the HAVING clause
select course_id, count(*) enrolled_students
from inscription
where final_grade > 8
group by course_id
having count(*) > 1
I hope this really dummy example will help you, and thanks for reading!!
Top comments (2)
Nice explanation and example! π
Just one thing to add: WHERE and HAVING is not a Postgress feature but a general sql feature.
You are absolutely rigth... Thanks for your comment π€π€