Having (SQL)
{{short description |SQL clause}}
{{Use American English |date=February 2024}}
{{Use mdy dates |date=February 2024}}
{{More citations needed |date=February 2024}}
A HAVING clause in SQL specifies that an SQL SELECT statement must only return rows where aggregate values meet the specified conditions.{{rp|125–127}}
Use
HAVING and WHERE are often confused by beginners, but they serve different purposes. WHERE is taken into account at an earlier stage of a query execution, filtering the rows read from the tables. If a query contains GROUP BY, rows from the tables are grouped and aggregated. After the aggregating operation, HAVING is applied, filtering out the rows that don't match the specified conditions. Therefore, WHERE applies to data read from tables, and HAVING should only apply to aggregated data, which isn't known in the initial stage of a query.
To view the present condition formed by the GROUP BY clause, the HAVING clause is used.{{Clarify|reason=|date=September 2020}}
Examples
To return a list of department IDs whose total sales exceeded $1000 on the date of January 1, 2000, along with the sum of their sales on that date:
SELECT DeptID, SUM(SaleAmount)
FROM Sales
WHERE SaleDate = '2000-01-01'
GROUP BY DeptID
HAVING SUM(SaleAmount) > 1000
Referring to the sample tables in the Join example, the following query will return the list of departments which have more than 1 employee:
SELECT DepartmentName, COUNT(*)
FROM Employee
JOIN Department ON Employee.DepartmentID = Department.DepartmentID
GROUP BY DepartmentName
HAVING COUNT(*) > 1;
HAVING is convenient, but not necessary. Code equivalent to the example above, but without using HAVING, might look like:
SELECT * FROM (
SELECT DepartmentName AS deptNam, COUNT(*) AS empCount
FROM Employee AS emp
JOIN Department AS dept ON emp.DepartmentID = dept.DepartmentID
GROUP BY deptNam
) AS grp
WHERE grp.empCount > 1;
References
External links
- [http://www.databasejournal.com/features/mysql/article.php/3469351 The HAVING and GROUP BY SQL clauses] {{Webarchive|url=https://web.archive.org/web/20110603062905/http://www.databasejournal.com/features/mysql/article.php/3469351 |date=2011-06-03 }}
- [https://www.w3schools.com/sql/sql_functions.asp SQL Aggregate Functions] {{Webarchive|url=https://web.archive.org/web/20170503060710/https://www.w3schools.com/sql/sql_functions.asp |date=May 3, 2017 }}
{{SQL}}
{{DEFAULTSORT:Having (Sql)}}