SQL HAVING Clause
Video Tutorial
Overview
The HAVING clause in SQL is used to filter the results obtained by the GROUP BY clause based on some specific conditions. Both HAVING and WHERE clauses are used to filter the data in SQL queries. But the WHERE clause can not be used with aggregate functions which is why the HAVING clause is needed.
Scope
- This article explains the working of HAVING clause in SQL with several examples and how it is different from the WHERE clause.
- This article does not explain the working of WHERE clause.
What is HAVING Clause in SQL?
The HAVING clause is generally used along with the GROUP BY clause. HAVING clause is used to filter the results obtained by the GROUP BY clause based on some specific conditions. HAVING clause is quite similar to the WHERE clause as both are used to filter records in SQL queries. But WHERE clause can not be used with aggregate functions (Eg: COUNT, MAX, SUM, etc) which is why HAVING clause is needed.
Some important points related to the HAVING clause are mentioned below:
- HAVING clause can only be used with the SELECT clause.
- In a query, the HAVING clause is placed after the GROUP BY clause and before the ORDER BY clause.
- GROUP BY clause is used to arrange the data into groups.
- HAVING clause is used in the column operation.
- The expression in the syntax of the HAVING clause can only contain constants.
Syntax
Examples
We will discuss two examples with different types of queries to understand how to use the HAVING clause in SQL.
Example 1:
Consider the following table named "Employees".
| EmpNo | EName | Job | Salary | DeptNo | Age |
|---|---|---|---|---|---|
| 1 | John | Clerk | 17000 | 10 | 25 |
| 2 | Harry | Clerk | 35000 | 20 | 27 |
| 3 | David | Manager | 78020 | 50 | 26 |
| 4 | Smith | Engineer | 77020 | 10 | 35 |
| 5 | Clarke | Salesman | 98020 | 20 | 32 |
| 6 | Musk | Engineer | 14000 | 50 | 45 |
Query 1: Let's say we want to display the Job types with total sum of salary greater than or equal to 90000. Then the query will be:
The output for the above query is given below
| Job | SUM(Salary) |
|---|---|
| Engineer | 91020 |
| Salesman | 98020 |
Query 2: Let's apply one more condition on the above query which is the age of the employee should be greater than or equal to 35.
The output for the above query is given below.
| Job | SUM(Salary) |
|---|---|
| Engineer | 91020 |
Query 3: Let's take another example where we want to display the Job types with total sum of salary greater than or equal to 60000. Here we will use ORDER BY clause along with the HAVING clause to sort the output according to the salary.
The output for the above query is given below
| Job | SUM(Salary) |
|---|---|
| Manager | 78020 |
| Engineer | 91020 |
| Salesman | 98020 |
Example 2:
In this example we will see how to use functions like COUNT and MAX with the HAVING clause. Consider the table given below named "Students".
| RollNo | Name | Subject | Marks |
|---|---|---|---|
| 15 | Jack | Mathematics | 99 |
| 20 | Henry | English | 89 |
| 23 | Mark | Physics | 90 |
| 4 | Steve | Mathematics | 69 |
| 17 | John | Physics | 95 |
| 36 | Mike | Chemistry | 50 |
| 33 | Tom | English | 75 |
Query 1: Let's say we want to show the subjects which are studied by more than one student. We will use the COUNT function with the HAVING clause in this type of query.
The output of the above query is given below
| Subject | COUNT(Subject) |
|---|---|
| Mathematics | 2 |
| Physics | 2 |
| English | 2 |
Query 2: Let's take another query in which we want to print the subjects in which the maximum marks obtained is greater than 90. For this we will use MAX function with the HAVING clause.
The output of the above query is given below.
| Subject | MAX(Marks) |
|---|---|
| Mathematics | 99 |
| Physics | 95 |
Difference between HAVING and WHERE CLAUSE
Both HAVING and WHERE clauses are used to filter the data in SQL queries but they still differ in many aspects which are mentioned below:
| HAVING | WHERE |
|---|---|
| The HAVING clause is used to filter data from groups according to the specified condition. | The WHERE clause is used to filter individual content from table according to the specified condition. |
| HAVING clause is used after the groups are made (Post-filter). | WHERE clause is used before the groups are made (Pre-filter). |
| HAVING clause needs to be executed with the GROUP BY clause. | WHERE clause can be executed without the GROUP BY clause. |
| In SQL queries, the HAVING clause is used after the GROUP BY clause. | In SQL queries, the WHERE clause is used before the GROUP BY clause. |
| HAVING clause can only be used with the SELECT statement for filtering the data. | WHERE clause can be used with SELECT, UPLOAD and DELETE statements. |
| SQL aggregate functions can be used with the HAVING clause in a query. | SQL aggregate functions can not be used with the WHERE clause in a query. |
| HAVING clause is used in column operations. | WHERE clause is used in row operations. |
Conclusion
- The HAVING clause in SQL is used to filter results obtained by the GROUP BY clause based on some specific conditions.
- The HAVING clause is similar to the WHERE clause but the HAVING clause is used to filter groups while the WHERE clause is used to filter individual content from the table.
- In a query, the HAVING clause is placed after the GROUP BY clause and before the ORDER BY clause.
- The HAVING clause can only be used with the SELECT clause.