What is the difference between the WHERE clause and the HAVING clause?

5/5/2022

Comparison of WHERE and HAVING clauses in SQL with examples and use cases

Go Back

Difference Between WHERE and HAVING Clause in SQL

SQL provides two important clauses for filtering data: WHERE and HAVING. While both are used to filter records in SQL queries, they serve different purposes and work at different stages of query execution. Understanding the key differences between them can help in writing efficient SQL queries.

Comparison of WHERE and HAVING clauses in SQL with examples and use cases

Key Differences Between WHERE and HAVING Clause

FeatureWHERE ClauseHAVING Clause
UsageUsed with SELECT, UPDATE, and DELETE statementsUsed only with the SELECT statement
Aggregate FunctionsCannot use aggregate functions unless in a subqueryCan use aggregate functions directly
Column RestrictionsCan use any column in the tableCan use columns but must be included in the GROUP BY clause
Execution OrderApplied before the GROUP BY clauseApplied after the GROUP BY clause
Effect on DataFilters individual rows before aggregationFilters grouped records after aggregation
PerformanceMore efficient as it works on raw dataWorks on grouped data, which may be less efficient

Examples of WHERE and HAVING Clause

Using WHERE Clause

The WHERE clause is used to filter records before performing any grouping.

SELECT * FROM Employees
WHERE Salary > 50000;

Using HAVING Clause

The HAVING clause is used to filter grouped records after applying aggregate functions.

SELECT Department, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department
HAVING AVG(Salary) > 60000;

Execution Flow in SQL Queries

  1. The WHERE clause filters individual records before applying GROUP BY.
  2. The GROUP BY clause groups the filtered data.
  3. The HAVING clause filters the grouped records based on aggregate functions.
  4. The final results are displayed based on the SELECT query.

Conclusion

The WHERE clause is used to filter raw data, while the HAVING clause is used to filter grouped data after aggregation. Understanding when to use each clause is crucial for writing optimized SQL queries. Use WHERE for filtering individual rows and HAVING when dealing with aggregated data.

By efficiently using WHERE and HAVING, you can enhance query performance and retrieve precise data results.

Ponit to be remember

  1. WHERE clause can be used with a Select, Update and Delete Statement Clause but the HAVING clause can be used only with a Select statement.
  2. We can't use an aggregate functions in the WHERE clause unless it is in a sub-query contained in a HAVING clause whereas we can use an aggregate function in the  HAVING clause. We can use a column name in the HAVING clause but the column must be contained in the group by clause.
  3.  WHERE is used before the GROUP BY clause whereas a HAVING clause is used to impose a condition on the GROUP Function and is used after the GROUP BY clause in the query.
  4.  A WHERE clause applies to each and every row whereas a HAVING clause applies to summarized rows (summarized with GROUP BY).
  5.  In the WHERE clause the data that is fetched from memory depending on a condition whereas in HAVING the completed data is first fetched and then separated  depending on the condition.

Table of content