Detailed explanation of the HAVING clause in SQL that cannot be underestimated

In this article, we will dig into the functionality of HAVING to understand its usage and how it differs from the WHERE clause. Through many practical cases, we will fully analyze the practical application of HAVING in SQL.

HAVING clause overview

HAVINGThe clause is specifically used to filter aggregated data, i.e. it is used forGROUP BYThe result set of the statement. When executing SQL queries, we usually useWHEREFilter the original data and then useHAVINGFurther filter the grouped data.

1. Basic syntax of HAVING

SELECT List name, Aggregation function
FROM Table name
GROUP BY List name
HAVING condition;

2. The difference between WHERE and HAVING

In SQL query optimization, it is recommended to use it as much as possible.WHEREConduct preliminary screening to reduceGROUP BYThe amount of data to be processed to improve query efficiency.

Application example of HAVING clause

Case 1: Calculate the number of employees in each department and filter departments with more than 3 employees

SELECT department, COUNT(*) AS emp_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 3;

📌Analysis:

• GROUP BY departmentGrouped by department.

• COUNT(*)Calculate the number of employees in each department.

• HAVING COUNT(*) > 3Only departments with more than 3 employees are retained.

Case 2: Screening departments with average salary above 5,000

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 5000;

📌Analysis:

• calculatedepartmentAverage salary within the group.

• HAVINGFilter out departments with average salary less than 5,000.

Case 3: Using WHERE and HAVING at the same time

SELECT department, SUM(salary) AS total_salary
FROM employees
WHERE salary > 3000
GROUP BY department
HAVING SUM(salary) > 20000;

📌Analysis:

• WHERE salary > 3000First screen employees whose salary is higher than 3,000.

• GROUP BY departmentGrouped by department.

• HAVING SUM(salary) > 20000Only departments with total salary of more than 20,000 are retained.

Advanced usage of HAVING clauses

1. Use multiple conditions to filter

SELECT department, COUNT(*) AS emp_count, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 5 AND AVG(salary) > 6000;

📌Analysis:

• HAVINGSupport logical operatorsAND、ORCombining multiple conditions.

• Filter out departments with less than 5 employees or an average salary of less than 6,000.

2. Use HAVING for range filtering

SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department
HAVING SUM(salary) BETWEEN 10000 AND 50000;

📌Analysis:

• HAVING SUM(salary) BETWEEN 10000 AND 50000Filters: Departments with total salary ranging from 10,000 to 50,000.

3. Sort with ORDER BY

SELECT department, COUNT(*) AS emp_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 3
ORDER BY COUNT(*) DESC;

📌Analysis:

• ORDER BY COUNT(*) DESCArrange in descending order of employees to quickly view the largest department.

Optimization strategy for HAVING clauses

• Try to use WHERE for preliminary screening, reduce data scale, and increaseGROUP BYComputational efficiency.

• Avoid complex calculations in HAVING statements, and can store the calculation results in a temporary table to improve query performance.

• Use index optimization grouping fields ifGROUP BYWhen it comes to fields in large tables, you can consider creating indexes to improve query speed.

Summarize

• HAVINGMainly used to screen the aggregation results, andWHEREUsed for filtering single-line data.

• When optimizing SQL query, it is recommended to use it firstWHEREFilter the original data, and thenHAVINGPerform aggregated data filtering.

• HAVINGApplicable to filtering of aggregation functions such as COUNT, SUM, AVG, MAX, MIN, etc., in conjunction withORDER BYIt can more conveniently analyze data.

Through reasonable useHAVING, can effectively optimize SQL queries and improve the efficiency of database operations.

This is the end of this article about detailed explanations of the HAVING clauses in SQL that cannot be underestimated. For more related SQL HAVING clauses, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!