WHERE is used to filter rows before any grouping or aggregation happens, while HAVING is used to filter groups or aggregated results after GROUP BY.
WHERE Clause in SQL
WHERE Clause is used to filter the records from the table or used while joining more than one table. Only those records will be extracted who are satisfying the specified condition in the WHERE clause. It can be used with SELECT, UPDATE, and DELETE statements. It is applied before any grouping or aggregation occurs in a query
Example: Using the WHERE Clause
Consider the following Student table. The table below contains details of students, including their roll numbers, names, and ages. We can use SQL queries to filter, sort, or retrieve specific data from this table based on various conditions.
| Roll_no | S_Name | Age |
|---|---|---|
| 1 | a | 17 |
| 2 | b | 20 |
| 3 | c | 21 |
| 4 | d | 18 |
| 5 | e | 20 |
| 6 | f | 17 |
| 7 | g | 21 |
| 8 | h | 17 |
Filtering Students Aged 18 or Above
In this example, it effectively retrieves students aged 18 or older, excluding those who do not meet the criteria. This approach ensures a refined and focused result set.
Query:
SELECT S_Name, Age
FROM Student
WHERE Age >=18
Output
| S_Name | Age |
|---|---|
| b | 20 |
| c | 21 |
| d | 18 |
| e | 20 |
| g | 21 |
Explanation:
- The query selects the columns S_Name and Age from the Student table.
- The WHERE clause filters rows where the Age is greater than or equal to 18.
- Only rows satisfying the condition are included in the result set.
HAVING Clause
HAVING Clause is used to filter the records from the groups based on the given condition in the HAVING Clause. Those groups who will satisfy the given condition will appear in the final result. It is applied after the grouping and aggregation of data.
Query:
SELECT Age, COUNT(Roll_No) AS No_of_Students
FROM Student GROUP BY Age
HAVING COUNT(Roll_No) > 1
Output
Age | No_of_Students |
|---|---|
17 | 3 |
20 | 2 |
21 | 2 |
Difference Between WHERE and HAVING Clause in SQL
Before going into examples, it’s important to understand the distinctions between the WHERE and HAVING clauses. The table below outlines their key differences for a clear comparison.
| WHERE Clause | HAVING Clause |
|---|---|
| Filters rows before groups are aggregated. | Filters groups after the aggregation process.. |
| WHERE Clause can be used without GROUP BY Clause | HAVING Clause can be used with GROUP BY Clause |
| WHERE Clause implements in row operations | HAVING Clause implements in column operation |
| WHERE Clause cannot contain aggregate function | HAVING Clause can contain aggregate function |
| WHERE Clause can be used with SELECT, UPDATE, DELETE statement. | HAVING Clause can only be used with SELECT statement. |
| WHERE Clause is used before GROUP BY Clause | HAVING Clause is used after GROUP BY Clause |
| WHERE Clause is used with single row function like UPPER, LOWER etc. | HAVING Clause is used with multiple row function like SUM, COUNT etc. |