Having and Where clause in PostgreSQL
This article explains the difference between the
Having clause in PostgreSQL . While they both have similar function, they both serve different purposes.
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:
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;
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
WHERE last_name ILIKE 'A%' AND sales_amount > 3.00
ORDER BY sales_amount;
The query above would return;
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
The syntax of a
HAVING clause in a query is as follows;
SELECT column_list, aggregate_function(column_name)
GROUP BY column_list
Below is a sample usage of the
Having 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
GROUP BY employee_id
HAVING sum(sales_amount) > 3.00;
and would return;
Thanks for reading.