SQL HAVING Clause

The SQL HAVING clause is used in combination with the GROUP BY clause to specify a filter condition for a group.

The HAVING clause can only be used with the SELECT.

The HAVING clause places filters on groups created by the GROUP BY clause, whereas the WHERE clause places filters on the selected columns.

Syntax

The syntax of the HAVING clause is as follows:

SELECT column_list
FROM tables
WHERE condition
GROUP BY column_list
HAVING condition
ORDER BY column_list;

Note: A SELECT statement can contain both both a WHERE and a HAVING clause, in that case the WHERE clause must appear before the GROUP BY clause and the HAVING clause must follow the GROUP BY in the query and must precede the ORDER BY clause if used.

Demo Table

Let us suppose that we have the following "Employee" and "Department" tables:

• Table: Employee:

  +--------+-------------+-----------+-----------+---------+
  | emp_no | first_name  | last_name | salary    | dept_id |
  +--------+-------------+-----------+-----------+---------+
  | 1001   | James       | Smith     | 6600      | 1       |
  | 1002   | Maria       | Martinez  | 9000      | 2       |
  | 1003   | Andrew      | Rodriguez | 9500      | 3       |
  | 1004   | Robert      | Hernandez | 9600      | 2       |
  | 1005   | Donna       | Williams  | 7400      | 5       |
  | 1006   | James       | Johnson   | 8000      | 4       |
  | 1007   | Stephanie   | Miller    | 8500      | 8       |
  | 1008   | Nelson      | Lopez     | 7300      | 8       |
  +--------+-------------+-----------+-----------+---------+
  

• Table : Department:

  +---------+------------------+
  | dept_id | dept_name        |
  +---------+------------------+
  | 1       | Customer Service |
  | 2       | Development      |
  | 3       | Finance          |
  | 4       | Human Resources  |
  | 5       | Marketing        |
  | 6       | Production       |
  | 7       | Research         |
  | 8       | Sales            |
  +---------+------------------+
  

SQL HAVING Examples

The following SQL statement will return the names of departments in which there is just one employee.

SELECT dep.dept_name AS "Department name", COUNT(em.emp_no) AS "Total employees"
FROM employee AS em 
INNER JOIN department AS dep ON em.dept_id = dep.dept_id
GROUP BY dep.dept_name
HAVING COUNT(em.emp_no) = 1

After executing the above SQL statement, the result set will be as follows:

+------------------+-----------------+
| Department name  | Total employees |
+------------------+-----------------+
| Customer Service | 1               |
| Finance          | 1               |
| Marketing        | 1               |
| Human Resources  | 1               |
+------------------+-----------------+

If you want to select the department names where there are no employees, you can use the following SQL statement:

SELECT dep.dept_name AS "Department name", COUNT(em.emp_no) AS "Total employees"
FROM employee AS em 
RIGHT JOIN department AS dep ON em.dept_id = dep.dept_id
GROUP BY dep.dept_name
HAVING COUNT(em.emp_no) = 0

After running the above statement, the output will be as follows:

+-----------------+-----------------+
| Department name | Total employees |
+-----------------+-----------------+
| Production      | 0               |
| Research        | 0               |
+-----------------+-----------------+