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.

SELECT
   column_1,
   aggregate_function (column_2)
FROM
   tbl_name
GROUP BY
   column_1
HAVING
   condition;

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

Image from Gyazo

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

SELECT   id,  SUM (marks) FROM student
GROUP BY   id HAVING
   SUM (marks) > 60;

Results

id | sum
    -- -- + -- -- -
    5 | 68
6 | 68
7 | 68
1 | 68
3 | 90(5 rows)

Image from Gyazo

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

SELECT  name, age, marks
FROM student
WHERE marks >60;

Results

name   | age | marks
---------+-----+-------
  tito   |  15 |    68
  SAILOR |  14 |    90
  tito   |  15 |    68
  ticoon |  15 |    68
  kito   |  15 |    68
(5 ROWS)

Image from Gyazo

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.

SELECT  name, age, marks
FROM student
WHERE marks >60 AND name LIKE '%ti%';

Results

Image from Gyazo

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

SELECT  name,
SUM(marks)
FROM student
WHERE marks >60
GROUP BY name;

Image from Gyazo

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.

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.