DEV Community

Cover image for Where vs Having in Postgress
sabrinasuarezarrieta
sabrinasuarezarrieta

Posted on

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.

Alt Text
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
Enter fullscreen mode Exit fullscreen mode

Alt Text

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 
Enter fullscreen mode Exit fullscreen mode

Alt Text

I hope this really dummy example will help you, and thanks for reading!!

Top comments (2)

Collapse
 
sbittis profile image
Sebastian Bittis

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

Collapse
 
sabrinasuarezarrieta profile image
sabrinasuarezarrieta

You are absolutely rigth... Thanks for your comment πŸ€—πŸ€—