HAVING Clause

The HAVING Clause is executed within the non-relational layer of a SQL query and acts as a filtering mechanism similar to the WHERE clause. Once the GROUP BY clause is set, the HAVING clause can be used and filters columns either defined in the grouping or are the result of an aggregate function. Aggregates are not allowed to be used in the WHERE clause meaning that the HAVING clause is the only standard query clause that will filter this type of information. The reason why aggregates cannot be used within the WHERE clause is because aggregations are executed after the result set has been generated from the relational layer portion of the query. Provided below is an example of using the HAVING clause in which we are trying to retrieve the departments that have more than 12 employees.

Given the following tables:

Employee

"id"	"first_name"	"department_id"
1	"Nanni"	4
2	"Odell"	1
3	"Bea"	2
…	…	…
45	"Pam"	1
46	"Sula"	2
47	"Margareta"	4

Department

id	name
1	Sales
2	Engineering
3	Data
4	Marketing

The following query will output a result set where the COUNT(department_id) > 12:

SELECT name
    , COUNT(department_id) AS employee_count
FROM employee emp
JOIN department depart
    ON depart.id = emp.department_id
GROUP BY 1
HAVING COUNT(department_id) > 12

Result Set:

name	employee_count
Data	14
Engineering	14

Notice that despite having four departments in the department table, we only returned two in the result set.

Uncommon Case: HAVING without GROUP BY

While HAVING is almost only utilized when filtering by the conditions mentioned above, it is possible to use HAVING without the GROUP BY clause. This method doesn’t have many real world cases, but it is commonly used to get table schemas, run logic tests where the outcome is always true or for more nefarious purposes such as SQL injection (hacking a database). Provided below is an example of HAVING without a GROUP BY clause.

Query

SELECT 1
HAVING 1=1;

Result Set

?column?
1

Data