MySQL HAVING Clause

The `HAVING` clause in MySQL is used to filter records that work on aggregated data returned by `GROUP BY`. It allows you to specify conditions that determine which group results are included in the output.

Usage

The `HAVING` clause is applied after `GROUP BY` to filter data based on aggregate functions like `SUM`, `COUNT`, or `AVG`. Unlike the `WHERE` clause, which cannot operate on aggregated data and is used for row-level filtering, `HAVING` is specifically designed for aggregate-based conditions.

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING condition;

In this syntax, `HAVING condition` specifies the aggregate-based condition to filter grouped results.

Note: In the execution order of SQL clauses, `HAVING` is processed after `GROUP BY` and before `ORDER BY`.

Examples

1. Basic HAVING Usage

SELECT department, COUNT(employee_id) AS num_employees
FROM employees
GROUP BY department
HAVING num_employees > 5;

This example retrieves departments with more than five employees by using `HAVING` to filter the aggregated result.

2. Using HAVING with SUM

SELECT customer_id, SUM(order_total) AS total_spent
FROM orders
GROUP BY customer_id
HAVING total_spent > 1000;

Here, the `HAVING` clause filters out customers who have spent more than $1000 in total.

3. Multiple Conditions in HAVING

SELECT product_category, AVG(price) AS avg_price
FROM products
GROUP BY product_category
HAVING avg_price BETWEEN 100 AND 500 AND COUNT(product_id) > 10;

This example uses multiple conditions in `HAVING` to find product categories with an average price between $100 and $500 and more than ten products.

4. Using HAVING with ORDER BY

SELECT department, COUNT(employee_id) AS num_employees
FROM employees
GROUP BY department
HAVING num_employees > 5
ORDER BY num_employees DESC;

This example demonstrates using `ORDER BY` with `HAVING`, sorting the results of departments with more than five employees in descending order by the number of employees.

Tips and Best Practices

• Use `HAVING` for aggregate filtering. Apply `HAVING` when filtering results based on aggregated data, not for row-level filtering.
• Combine with `GROUP BY`. Always use `HAVING` with `GROUP BY` because `HAVING` operates on grouped data.
• Optimize with indexes. Ensure efficient query execution by using indexes on columns involved in `GROUP BY`.
• Limit complex conditions. Keep `HAVING` conditions simple to maintain performance and readability.