SQL HAVING: The Ultimate Guide | HAVING vs. WHERE

Summary: this tutorial introduces you to the SQL HAVING clause that allows you to specify a condition for the groups summarized by the GROUP BY clause.

Introduction to SQL HAVING clause

In the previous tutorial, you have learned how to use the GROUP BY clause to summarize rows into groups and apply the aggregate function such as MIN, MAX, SUM, COUNT, AVG to each group.

To specify a condition for groups, you use the HAVING clause.

The HAVING clause is often used with the GROUP BY clause in the SELECT statement. If you use a HAVING clause without a GROUP BY clause, the HAVING clause behaves like the WHERE clause.

The following illustrates the syntax of the HAVING clause:

SELECT

	column1,
	column2,
	AGGREGATE_FUNCTION (column3)





FROM
	table1
GROUP
 
BY
	column1,
	column2
HAVING
	group_condition;
Code language:
 
SQL (Structured Query Language)
 
(
sql
)

Note that the HAVING clause appears immediately after the GROUP BY clause.

HAVING vs. WHERE

The WHERE clause applies the condition to individual rows before the rows are summarized into groups by the GROUP BY clause. However, the HAVING clause applies the condition to the groups after the rows are grouped into groups.

Therefore, it is important to note that the HAVING clause is applied after whereas the WHERE clause is applied before the GROUP BY clause.

SQL HAVING clause examples

We will take the employees and departments tables in the sample database for the demonstration.

To get the managers and their direct reports, you use the GROUP BY clause to group employees by the managers and use the COUNT function to count the direct reports.

The following query illustrates the idea:

SELECT
 
    manager_id,
    first_name,
    last_name,
    
COUNT
(employee_id) direct_reports
FROM
    employees
WHERE
    manager_id 
IS
 
NOT
 
NULL
GROUP
 
BY
 manager_id;
Code language:
 
SQL (Structured Query Language)
 
(
sql
)

Try It

To find the managers who have at least five direct reports, you add a HAVING clause to the query above as the following:

SELECT
 
    manager_id,
    first_name,
    last_name,
    
COUNT
(employee_id) direct_reports
FROM
    employees
WHERE
    manager_id 
IS
 
NOT
 
NULL
GROUP
 
BY
 manager_id
HAVING
 direct_reports >= 
5
;
Code language:
 
SQL (Structured Query Language)
 
(
sql
)

Try It

SQL HAVING with SUM function example

The following statement calculates the sum of salary that the company pays for each department and selects only the departments with the sum of salary between 20000 and 30000.

SELECT
 
    department_id, 
SUM
(salary)
FROM
    employees
GROUP
 
BY
 department_id
HAVING
 
SUM
(salary) 
BETWEEN
 
20000
 
AND
 
30000
ORDER
 
BY
 
SUM
(salary);
Code language:
 
SQL (Structured Query Language)
 
(
sql
)

Try It

SQL HAVING with MIN function example

To find the department that has employees with the lowest salary greater than 10000, you use the following query:

SELECT

	e.department_id,
	department_name,
	
MIN
(salary)
FROM
	employees e
INNER
 
JOIN
 departments d 
ON
 d.department_id = e.department_id
GROUP
 
BY
	e.department_id
HAVING
	
MIN
(salary) >= 
10000
ORDER
 
BY
	
MIN
(salary);
Code language:
 
SQL (Structured Query Language)
 
(
sql
)

Try It

How the query works.

1. First, use the GROUP BY clause to groups employees by department.
2. Second, use the MIN function to find the lowest salary per group.
3. Third, apply the condition to the HAVING clause.

SQL HAVING clause with AVG function example

To find the departments that have the average salaries of employees between 5000 and 7000, you use the AVG function as the following query:

SELECT

	e.department_id,
	department_name,
	
ROUND
(
AVG
(salary), 
2
)
FROM
	employees e
INNER
 
JOIN
 departments d 
ON
 d.department_id = e.department_id
GROUP
 
BY
	e.department_id
HAVING
	
AVG
(salary) 
BETWEEN
 
5000
AND
 
7000
ORDER
 
BY
	
AVG
(salary);
Code language:
 
SQL (Structured Query Language)
 
(
sql
)

Try It

In this tutorial, you have learned how to use the SQL HAVING clause to apply the condition to groups.

Was this tutorial helpful ?