Understand The Difference Between HAVING and WHERE Clause In SQL
In this article, I am going to discuss one of the most important topics difference between HAVING and WHERE Clause in SQL with an example. Please, first of all read the Having Clause and Where Clause articles before proceeding to this topic. Let us understand the Difference Between Where and Having a Clause in SQL Server with an example.
Difference Between HAVING and WHERE Clause
| WHERE Clause | HAVING Clause |
|---|---|
| This is used to filter the records or data from the table based on a specific condition. | Whereas this can be used to filter out records from the groups based on a specific condition. |
| This can be used without the ‘GROUP BY’ clause. | This can not be used without the ‘GROUP BY’ clause. |
| It will be used with row operations. | This will be working with the column operation. |
| It can not contain the aggregate functions. | Whereas it can contain aggregate functions. |
| The WHERE clause is used with the ‘SELECT’, ‘UPDATE’, and ‘DELETE’ statements. | HAVING Clause can only be used with the ‘SELECT’ statement. |
| It can be used before the ‘GROUP BY’ clause if needed. | Whereas, it will be used after the ‘GROUP BY’ keyword. |
| It is used with a single row function like ‘UPPER’, ‘LOWER’. | It can be used with multiple row functions like ‘SUM’, ‘COUNT’. |
1). WHERE Clause:
WHERE keyword is utilized to filter the records from the table or can be used while joining more than one table. Only those records will be extracted who are satisfying the specified or given condition in the WHERE keyword. This can be used with SELECT, UPDATE, DELETE statements.
Let us consider below table "Employees"
| Emp_ID | Emp_Name | Age |
|---|---|---|
| 1 | Emma Grate | 21 |
| 2 | Wiley Waites | 24 |
| 3 | Abby Normal | 25 |
| 4 | Faye Clether | 27 |
| 5 | Thornton | 19 |
| 6 | Anna Littlical | 20 |
| 7 | Stan Dupp | 28 |
| 8 | Mark Ateer | 21 |
| 9 | Ben Dover | 22 |
| 10 | Anne Teak | 26 |
Let’s consider the Query:
|
1 2 |
SELECT EMP_Name, Age FROM Employees WHERE Age >=22 |
2). HAVING Clause:
This HAVING keyword is utilized to filter the records or data from the groups based on the given condition in the HAVING keyword. Those groups who will satisfy the given or specified condition will appear in the final result. Whereas, the HAVING keyword can only be used with SELECT statement.
Let us consider the Employees table mentioned above and apply to have keyword on it:
|
1 2 3 |
SELECT Age, COUNT(EMP_ID) AS No_of_Employee FROM Employees GROUP BY Age HAVING COUNT(EMP_ID) > 1 |
Difference between Where and Having Clause in SQL:
- WHERE keyword is used to filter the records from the table based on the specified condition Whereas the HAVING keyword is used to filter records from the groups based on the specified condition.
- This WHERE keyword can be used without GROUP BY keyword Whereas the HAVING keyword can’t be used without GROUP BY keyword
- These WHERE keyword implements in row operations Whereas the HAVING keyword implements in column operation.
- It can’t contain aggregate function Whereas the HAVING keyword can contain aggregate function.
- WHERE keyword can be used with SELECT, UPDATE, DELETE statement Whereas the HAVING keyword can only be used with SELECT statement.
- This WHERE keyword is used before GROUP BY keyword Whereas the HAVING keyword is used after GROUP BY keyword
- WHERE keyword is used with single row functions such as UPPER, LOWER, etc. Whereas the HAVING keyword is used with multiple row functions such as SUM, COUNT, etc.
About Author
