The HAVING clause is just like the WHERE clause. Both the HAVING and the WHERE clauses are used to display the results that are based on certain specific condition.
The only difference between the HAVING clause and the WHERE clause is that the HAVING clause is concerned with groups and the WHERE clause is concerned with the individual rows.
The having clause can receive inputs from the FROM, WHERE, and the GROUP BY clause. The following points should be kept in mind:
- If the user specifies the GROUP BY clause, then the HAVING clause must be applied to the group created by the GROUP BY clause.
- If the user specifies the WHERE clause but does not specify the GROUP BY clause then HAVING clause should be applied to the output obtained by the WHERE clause and that output should be treated as one group.
- If the user does not specify a WHERE clause and the GROUP BY clause then the HAVING clause should be applied to the output that is obtained by the FROM clause and this output should be treated as one single group.
SELECT column-name, function (column-name)
WHERE column-name condition
Group by column-name
HAVING function (column-name) condition
EXAMPLE of HAVING CLAUSE:
Suppose you have a table that is named STUDENT as follows:
Let suppose we are searching for the student whose STUD_ID that is student id is more than 150. Then the query will be as follows:
FROM student group by name
Having count (STUD_ID) > 150
The above query will generate the following result: