Having and Where clause in PostgreSQL

Photo by Caspar Camille Rubin on Unsplash

This article explains the difference between the where and Having clause in PostgreSQL . While they both have similar function, they both serve different purposes.

Where clause

A where clause allows us to filter rows based on specific conditions. The filtering occurs before any groupings are made.

The conditions in the Where clause can be formed using comparison and logical operators such as the =, >, <, !=, & and | e.t.c

When we have a query with the Where clause, the execution takes place in this order From -> Where -> Select -> Order by . The order by clause is used to sort the query.

Additionally, when a column alias is used in the Select clause, that same alias cannot be used in the Where clause because of the order of execution mentioned above.

The syntax of a Where clause in a select query:

SELECT column_list
FROM table
WHERE condition
ORDER BY sort_method;

Here is an example of how a Where clause works. Assuming we have a table called “Employee table” as shown below;

Employee_table

To find all employees whose last name starts with an “ A” and have transactions above $3.00, we can use the below query:

SELECT employee_id, last_name, sales_amount as transactions
FROM Employee
WHERE last_name ILIKE 'A%' AND sales_amount > 3.00
ORDER BY sales_amount;

The query above would return;

Having Clause

The HAVING clause is used with the GROUP BY clause to filter groups or aggregates based on specific conditions and it usually comes after the GROUP BY clause. The HAVING clause is similar to the WHERE clause except that it operates on groups. It is valid to say that HAVING is the WHERE for groups.

The execution of a query with the HAVING clause takes place in this order; FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY.

In addition, you cannot use column aliases in the HAVING clause since it is evaluated before the availability of the column alias in the SELECT statement.

The syntax of a HAVING clause in a query is as follows;

SELECT column_list, aggregate_function(column_name)
FROM table
GROUP BY column_list
HAVING condition;

Below is a sample usage of theHaving clause using the below Employees table;

In the above table, we can see that an employee_id can occur multiple times for each time the employee makes a transaction.

To find all the employees who have done transactions with combined amount > $3.00, we can use the query below:

SELECT employee_id, sum(sales_amount) as Transactions
FROM Employee
GROUP BY employee_id
HAVING sum(sales_amount) > 3.00;

and would return;

Sample Having clause usage

Thanks for reading.