The "WHERE" and "HAVING" clauses in PostgreSQL let us filter the table’s data. Both these clauses retrieve only those rows that satisfy the specified condition. However, both these clauses are distinct and are used for different purposes. The primary difference is that the "WHERE" clause filters the data of each row and then groups the data while the "HAVING" clause filters the grouped data.

This write-up will illustrate the difference between the "WHERE" and "HAVING" clauses along with syntax and suitable examples.

PostgreSQL WHERE CLause

A "WHERE" clause filters a result set before grouping the data. This means the "WHERE" clause first filters the data based on the specified condition and then groups the filtered data.

The following syntax is used to execute the "WHERE" clause in PostgreSQL:

SELECT column_list
FROM table_name
WHERE filter_condition
GROUP BY column_name;

Sample Table:

Let’s see how the "WHERE" clause works in PostgreSQL using the following example:

SELECT car_model, SUM (car_price)
 FROM cars_info
 WHERE car_price >= 1500000
 GROUP BY car_model;

In this example,

- We use the "SELECT" query to fetch the "car_model" and sum of "car_price" from the "cars_info" table.

- Next, we use the "WHERE" clause to filter the table’s data based on the car's price (i.e., car_price >= 150000).

After this, we employ the "GROUP BY" clause to group the table’s records based on the car model.

From the output, it is clear that the "WHERE" clause gets executed first and the table’s data is filtered accordingly. After this, the "GROUP BY" clause executes and groups the data according to the car model.

PostgreSQL HAVING CLause

PostgreSQL offers a "HAVING" clause that lets us filter a result set based on the specified condition. It filters the table’s data as a group. This clause was introduced in SQL because the "WHERE" clause can’t be used with the aggregate functions.

To use the "HAVING" clause in PostgreSQL, follow the below-mentioned syntax:

SELECT column_list, aggregate_function
 FROM tab_name
 GROUP BY column_name
HAVING filter_condition;

Let’s see how the "HAVING" clause works in PostgreSQL using the following example:

SELECT car_model, SUM (car_price)
 FROM cars_info
 GROUP BY car_model
 HAVING SUM(car_price) >= 1500000;

In this code,

- After fetching the car’s model and the sum of the car’s price we grouped the table’s data according to the car’s model.

- In the next line, we use the "HAVING" clause with the SUM function to filter the table’s data based on the condition “sum of car_price must be greater than or equal to 1500000”.

From the output, you can observe that the "GROUP BY" clause executes first and it groups the table’s data according to the car’s model. After this, the data is filtered according to the grouped data, instead of each row.

PostgreSQL HAVING Vs WHERE Clause - What’s the Difference?

Here are some notable differences between the PostgreSQL "HAVING" and "WHERE" clauses:

• The "WHERE" clause/keyword filters each table row before grouping while the "HAVING" clause filters the grouped data.
• PostgreSQL runs "WHERE" before "GROUP BY" while "HAVING" is executed after the "GROUP BY".
• In PostgreSQL, aggregate methods are not permitted in the "WHERE" clause.
• The "HAVING" clause is most commonly used to filter groups based on aggregate functions, but it can also be used without aggregate functions.
• If the "HAVING" clause is used without an aggregate function, it works similarly to the "WHERE" clause in filtering data.

Conclusion

In PostgreSQL, the "WHERE" and "HAVING" clauses are used to filter the result set of the "SELECT" query. However, the "WHERE" clause can’t be used with the aggregate functions while the "HAVING" clause can be used with any aggregate function.

Moreover, the "HAVING" clause filters the grouped data while the "WHERE" clause filters every single row before grouping the data. This post used suitable examples to discuss how the "HAVING" clause differs from "WHERE".