MySQL HAVING Clause Explained

This article explains the function of the HAVING clause in MySQL, focusing on how it filters aggregated data generated by the GROUP BY clause. You will learn the fundamental purpose of the HAVING clause, how it differs from the WHERE clause, and how to apply it in your SQL queries with practical examples.

The Function of the HAVING Clause

In MySQL, the HAVING clause is used to filter the results of a query after the rows have been grouped by the GROUP BY clause. It allows you to specify search conditions for groups of rows or aggregate values (such as SUM, COUNT, AVG, MAX, or MIN), which is something the standard WHERE clause cannot do.

Without the HAVING clause, you would not be able to filter query results based on calculated, aggregated values.

HAVING vs. WHERE: The Key Difference

The primary difference between WHERE and HAVING lies in the order of execution within a MySQL query:

• WHERE clause: Filters individual rows before any grouping or aggregation takes place. It cannot be used with aggregate functions.
• HAVING clause: Filters grouped rows after the GROUP BY clause has aggregated the data. It is specifically designed to work with aggregate functions.

SQL Syntax with HAVING

The standard syntax for using the HAVING clause in a MySQL query is as follows:

SELECT column_name, AGGREGATE_FUNCTION(column_name)
FROM table_name
WHERE condition_for_individual_rows
GROUP BY column_name
HAVING aggregate_function_condition;

Practical Example

Consider an employees table that contains data about employees, their departments, and their salaries.

If you want to find all departments where the total salary expenditure is greater than $150,000, you must aggregate the salaries by department and then filter the results. You cannot use the WHERE clause for this filter because SUM(salary) is an aggregate function.

Instead, you use the HAVING clause:

SELECT department, SUM(salary) AS total_salaries
FROM employees
GROUP BY department
HAVING SUM(salary) > 150000;

Combining WHERE and HAVING

You can use both clauses in a single query to perform multi-stage filtering. For example, if you want to find the departments with a total salary expenditure over $150,000, but you only want to include full-time employees in the calculation:

SELECT department, SUM(salary) AS total_salaries
FROM employees
WHERE job_type = 'Full-Time'
GROUP BY department
HAVING SUM(salary) > 150000;

In this query: 1. The WHERE clause filters out any non-full-time employees first. 2. The GROUP BY clause groups the remaining employees by their department. 3. The SUM(salary) is calculated for each group. 4. The HAVING clause filters out departments whose calculated total salary is $150,000 or less.