SQL HAVING vs WHERE - What's the Difference?

Introduction

HAVING and WHERE clauses are used to filter rows resulting from select statement. HAVING clause is used to return the rows that meet a specific condition. Where clause is more of the same as HAVING but while it is used to filter through each row, the having clause filters grouped rows. Syntactically, the difference between the two clauses is that the GROUP BY clause occurs before HAVING clause while it occurs after the WHERE clause.

The HAVING clause is often used together with the GROUP BY clause when filtering the group of rows that do not meet some specific conditions. Below is an illustration of a typical syntax of a statement that is utilizing a HAVING clause.

From the above syntax, the having clause sets the conditions after the GROUP BY clause executes. On the other hand, the where clause sets the condition for before the rows are grouped with the GROUP BY clause. This is the major difference between the having clause and the where clause.
Alternatively, we can have the HAVING clause without the GROUP BY clause. In such a case the HAVING clause applies to the query as a single group. It is important to note that when using SELECT and HAVING clause together, you only refer to columns from within the aggregate functions.

Let’s have a look on a few examples using the Postgresql HAVING clause.

We first run a select statement on the student table to see the data

Then, we will run the SELECT clause together with the GROUP BY and the HAVING clause with the aggregate function SUM() as follows:

Results

The results indicate the groups of id’s with the marks grater that 60 as indicated on the image above.

WHERE clause

As indicated earlier, the WHERE clause filter the rows resulting from a select clause. It appears immediately after the FROM and works with logical operators returning true , false or unknown. I also works with Boolean expression using AND and OR operators. In our previous articles we have met the WHERE clause in combination with other condition clause. Let’s look at a few more examples using WHERE clause and logical operators.

Examples of Postgresql WHERE clause

Results

In our example, we sort for the details of the student whose marks are greater than 60 using the WHERE clause.

More examples

Let’s now look at an example using logical operator AND and LIKE conditions altogether.

Results

Finally, we will look at an application of WHERE clause together with GROUP BY clause

This query outputs the sum of marks for each student as entered into the student table based on the name of each student.

Conclusion

In this tutorial, we have focused on the differences and the application of the HAVING and the WHERE clause. We hope you can apply what you’ve learned to your application.