Having (SQL)
This articleneeds additional citations forverification.(February 2024) |
AHAVING
clause inSQLspecifies that an SQLSELECT
statement must only return rows where aggregate values meet the specifiedconditions.[1]: 125–127
Use
[edit]HAVING
andWHERE
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 containsGROUP 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, andHAVING
should only apply to aggregated data, which isn't known in the initial stage of a query.
To view the present condition formed by theGROUP BY
clause, theHAVING
clause is used.[clarification needed]
Examples
[edit]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:
SELECTDeptID,SUM(SaleAmount)
FROMSales
WHERESaleDate='2000-01-01'
GROUPBYDeptID
HAVINGSUM(SaleAmount)>1000
Referring to thesample tables in theJoinexample,the following query will return the list of departments which have more than 1 employee:
SELECTDepartmentName,COUNT(*)
FROMEmployee
JOINDepartmentONEmployee.DepartmentID=Department.DepartmentID
GROUPBYDepartmentName
HAVINGCOUNT(*)>1;
HAVING
is convenient, but not necessary. Code equivalent to the example above, but without usingHAVING
,might look like:
SELECT*FROM(
SELECTDepartmentNameASdeptNam,COUNT(*)ASempCount
FROMEmployeeASemp
JOINDepartmentASdeptONemp.DepartmentID=dept.DepartmentID
GROUPBYdeptNam
)ASgrp
WHEREgrp.empCount>1;
References
[edit]- ^PostgreSQL 16.1 Documentation(PDF).The PostgreSQL Global Development Group. 2023.RetrievedFebruary 8,2024.
External links
[edit]- The HAVING and GROUP BY SQL clausesArchived2011-06-03 at theWayback Machine
- SQL Aggregate FunctionsArchivedMay 3, 2017, at theWayback Machine