Where vs Having in Postgress

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!!

Comments

[Sebastian Bittis]

Nice explanation and example! 😊
Just one thing to add: WHERE and HAVING is not a Postgress feature but a general sql feature.

[sabrinasuarezarrieta]

You are absolutely rigth... Thanks for your comment 🤗🤗