Difference Between WHERE and HAVING Clause in SQL
FREE
The WHERE clause in SQL is used to filter records and restrict the rows returned by a query based on specified conditions. It helps in extracting only those records that fulfill a particular criterion, making it essential for precise data retrieval and manipulation, serving as a fundamental difference when comparing where vs having in SQL.
Where VS Having Clause in SQL
When working with SQL, understanding the difference between the WHERE and HAVING clauses is pivotal for effective data manipulation and retrieval. Both clauses serve as filters but in distinct contexts and stages of query execution.
Consider a demo table named Employees to illustrate the use of WHERE and HAVING clauses. This table contains the following columns: EmployeeID, Name, Department, and Salary.
| EmployeeID | Name | Department | Salary |
|---|---|---|---|
| 1 | John Doe | Marketing | 50000 |
| 2 | Jane Smith | Sales | 60000 |
| 3 | Mike Brown | IT | 70000 |
| 4 | Emily White | Marketing | 40000 |
| 5 | Alex Green | IT | 80000 |
What is a WHERE Clause?
The WHERE clause in SQL is a condition-based filter that is applied to rows returned by a SELECT statement. It specifies which rows to retrieve by including only those that meet a particular condition. This clause is fundamental for queries that require specific criteria to be met, enabling targeted data extraction from one or more tables based on the defined conditions.
Example: Let's use the above created table Employees and suppose we want to find employees in the IT department with a salary greater than 75000. We can construct a query with the WHERE clause as follows:
Output: After executing the query, we get the following result, which lists employees in the IT department earning more than 75000:
| EmployeeID | Name | Department | Salary |
|---|---|---|---|
| 5 | Alex Green | IT | 80000 |
This output clearly demonstrates how the WHERE clause filters the dataset based on specified conditions, in this case, department and salary, to return a more focused set of results.
What is a HAVING Clause?
The HAVING clause in SQL is used in conjunction with the GROUP BY statement to filter groups or aggregates based on a specified condition. Unlike the WHERE clause, which filters rows, the HAVING clause filters groups created by GROUP BY. This makes it especially useful for working with aggregated data, such as counts, sums, averages, etc., where you want to apply conditions to groups rather than individual records.
Example: Let's use the above created table Employees and suppose we want to find departments whose average salary is above $60,000. To accomplish this, we would first group the records by department and then use the HAVING clause to filter these groups:
Output: Given the provided data, every department's average salary exceeds $40,000, so we expect to see all departments listed, along with their average salaries:
| Department | AverageSalary |
|---|---|
| Marketing | 45000 |
| Sales | 60000 |
| IT | 75000 |
This output indicates that all departments—Marketing, Sales, and IT—have an average salary greater than $40,000, showcasing the HAVING clause's ability to filter groups after aggregation.
Difference between WHERE and HAVING Clause
Despite the fact that both HAVING and WHERE are used to filter the records and get only the required ones, there are some major differences between them that one should keep in mind to avoid mistakes while writing SQL queries. Some of the points that clearly elaborates on WHERE Vs HAVING in SQL are:
| Basis | WHERE clause | HAVING clause |
|---|---|---|
| Definition | used to filter individual rows | filters group results |
| Basic functionality | row operation | column operation |
| Aggregate functions | doesn't work with aggregate functions in conditions. Example: WHERE COUNT(column_name) > value X | works with aggregate functions in conditions. |
| Used with | SELECT, UPDATE and DELETE commands | only with SELECT statement |
| Data fetching | fetches only those records which fulfill the specified criteria | first creates individual groups and then filters out the results |
| Condition | works without GROUP BY clause | can't work without GROUP BY clause |
| GROUP BY | used before GROUP BY | used after GROUP BY |
| Type of Filter | works before grouping, hence as a PRE-FILTER | works after the groups are made, so it is referred to as POST-FILTER |
When to Use WHERE and HAVING Clauses?
After learning WHERE Vs HAVING in SQL, we can note that the main difference between the two helps us decide which clause to use when. If the filtration of records is to be done before grouping, WHERE is used. And to filter the rows after grouping them, HAVING is used.
Assuming that filtration can be done without forming the groups, WHERE should be used first as it improves the performance of the query and gives the results quickly. After that, aggregate functions such as COUNT() and SUM() etc. can be applied on those filtered rows.
Can We Use Them Together?
The answer to this is "YES". HAVING and WHERE can be used to apply both the filters in a SELECT command only. Initially, the rows are filtered based on WHERE conditions. Then the rows are grouped by the column mentioned and aggregations are performed. Eventually, the HAVING clause is used to filter the obtained aggregated result.
Example:
We are using the above table to demonstrate how we can use both the clauses together.
By this query, we are trying to get the total sales of cities Delhi and Mumbai only which have it greater than 10.
Learn More
Conclusion
In the above article, we have studied that:
- WHERE and HAVING clauses are both used for excluding some unwanted rows.
- Multiple points that supports WHERE Vs HAVING in SQL.
- The key difference between the two is WHERE clause doesn't incorporate aggregate functions while the HAVING clause does.
- Different operators can be used to make required conditions while filtering.
- When used together, WHERE performs first and keeps out the rows from the selection that don't fulfill the condition, and then HAVING does it functioning on the groups made.