The HAVING clause is typically used with the GROUP BY clause to specify a filter condition for a group or an aggregate. It's specifically designed for use in conjunction with the SELECT statement.
To understand this easily, let's consider the tables employees and departments.
+--------+--------------+------------+---------+ | emp_id | emp_name | hire_date | dept_id | +--------+--------------+------------+---------+ | 1 | Ethan Hunt | 2001-05-01 | 4 | | 2 | Tony Montana | 2002-07-15 | 1 | | 3 | Sarah Connor | 2005-10-18 | 5 | | 4 | Rick Deckard | 2007-01-03 | 3 | | 5 | Martin Blank | 2008-06-24 | NULL | +--------+--------------+------------+---------+ |
+---------+------------------+ | dept_id | dept_name | +---------+------------------+ | 1 | Administration | | 2 | Customer Service | | 3 | Finance | | 4 | Human Resources | | 5 | Sales | +---------+------------------+ |
|
Table: employees |
Table: departments |
Now, let's say instead of just retrieving the names of employees and their departments, you want to identify departments that have no employees.
For smaller tables, you could use a left join and manually check each department. However, for larger tables with thousands of employees, this approach becomes impractical.
In such cases, you can utilize the HAVING clause alongside the GROUP BY clause to achieve this:
SELECT t1.dept_name, count(t2.emp_id) AS total_employees
FROM departments AS t1 LEFT JOIN employees AS t2
ON t1.dept_id = t2.dept_id
GROUP BY t1.dept_name
HAVING total_employees = 0;If you run the statement above, the result will appear similar to this:
+------------------+-----------------+ | dept_name | total_employees | +------------------+-----------------+ | Customer Service | 0 | +------------------+-----------------+
Tip: The HAVING clause works much like a WHERE clause, but it operates on groups of rows rather than individual rows.