Overview
TheHAVING
clause specifies a search condition by using an aggregate function.
It will filter out the records returned from a GROUP BY
clause that do not fulfill a specified condition.
Differences Between WHERE and HAVING Clause
The following table will illustrate the differences between theHAVING
and WHERE
clause:
WHERE | HAVING |
---|---|
The GROUP BY clause appears after the WHERE clause. | The GROUP BY clause appears before the HAVING clause. |
The WHERE clause can’t work with an aggregate function. | The HAVING clause can work with an aggregate function. |
The WHERE clause filters particular records. | The HAVING clause filters the group of records. |
Syntax
The basic syntax of theGROUP BY
clause is as follows:
SELECT column_1, column_2,...
selects the columns you want to display.FROM table_name
selects the table where the data comes from.GROUP BY column_name(s)
lists the columns you want to group in the GROUP BY clause.HAVING condition_aggregate_function
provides the condition for filtering rows, which theGROUP BY
clause forms. The condition can use an aggregate function, such asSUM()
,COUNT()
,MIN()
, and so on.
Examples
Let’s assume that we have two tables here, the student table and the score table: student table#Case 1: HAVING
Clause with AVG
Function
The following example uses an AVG
aggregate function to filter the student ID with the subject which has an average score of more than 80:
#Case 2: HAVING
Clause with COUNT
Function
The following query lists the number of score statuses that have more than 2 “PASSED” values:
#Case 3: HAVING
Clause with MAX
Function
Let’s assume that the minimum score criteria is 75.
Here we will find the maximum score of each subject with the condition that it should be more than 75.
#Case 4: HAVING
with JOIN
Condition
Assume that you want to know which students have failed in their subject.
You can combine the student table with the score table using the JOIN
clause and apply a condition on the score_stat
column where the values should be equal to FAILED, as shown in the following query:
- The
JOIN
clause will combine the two tables. - Then, the
GROUP BY
clause will filter all records from both tables based on the specified columns. - The
HAVING
clause, then, will filter the records returned from theGROUP BY
clause according to the specified condition.