Understanding the Difference Between WHERE and HAVING in SQL

When it comes to filtering and selecting data from a database table, SQL provides two essential clauses: WHERE and HAVING. While both serve similar purposes, they are used in different contexts and operate on different parts of a SQL query. In this blog post, we will explore the differences between WHERE and HAVING and understand when to use each of them.

WHERE Clause

The WHERE clause is used in SQL to filter rows from a table based on specific conditions. It operates on the individual rows of the table and is primarily used with the SELECT, UPDATE, and DELETE statements. Here are a few key points about the WHERE clause:

a. Filtering Rows: The WHERE clause allows you to specify conditions that must be met for a row to be included in the result set. For example, you can filter rows based on values in one or more columns, comparisons between columns, or logical conditions.

b. Row-level Filtering: The WHERE clause filters rows before any grouping or aggregation is performed. It evaluates the conditions for each row individually and excludes rows that do not meet the specified criteria.

c. Use with SELECT, UPDATE, DELETE: The WHERE clause is commonly used with the SELECT statement to retrieve specific rows from a table. It can also be used with UPDATE and DELETE statements to modify or remove specific rows based on the given conditions.

d. Example :

SELECT 
       full_name
       , salary
FROM 
       [HR].[dbo].[emp_master]
WHERE 
       salary > 10000

This Query returns employees with salary greater than 10000

HAVING Clause

The HAVING clause is used in SQL to filter groups of rows based on conditions specified in an SQL query. It operates on the results of a GROUP BY clause and is primarily used with the SELECT statement. Let's take a closer look at the HAVING clause:

a. Filtering Groups: The HAVING clause allows you to filter groups of rows based on aggregated values or calculations performed on a specific column. It is typically used in conjunction with aggregate functions like COUNT, SUM, AVG, etc.

b. Group-level Filtering: The HAVING clause filters groups of rows after the GROUP BY clause has been applied. It evaluates the conditions for each group as a whole and excludes groups that do not meet the specified criteria.

c. Use with SELECT: The HAVING clause is used with the SELECT statement to filter groups of rows based on aggregate conditions. It helps in retrieving specific groups that satisfy the given criteria.

d. Example :

SELECT 
       department_name
       , AVG(salary) Avg_Salary
FROM 
       [HR].[dbo].[emp_master]
GROUP BY 
       department_name
HAVING 
       AVG(salary) > 10000 

This Query return departments with average salary more than 10000

Key Differences

Now that we have covered the basics of WHERE and HAVING clauses, let's summarize their key differences:

a. Operation: The WHERE clause filters individual rows, while the HAVING clause filters groups of rows.

b. Usage: WHERE is used with SELECT, UPDATE, and DELETE statements, whereas HAVING is used with SELECT statements that involve grouping of rows.

c. Timing: WHERE filters rows before any grouping or aggregation, while HAVING filters groups after the GROUP BY clause is applied.

d. Aggregation: WHERE cannot directly filter based on aggregate functions, while HAVING is specifically designed to filter groups based on aggregated values.

Conclusion

In SQL, the WHERE and HAVING clauses play distinct roles in filtering data. The WHERE clause operates on individual rows and is used to filter rows based on specific conditions. On the other hand, the HAVING clause filters groups of rows based on aggregate conditions.

That’s all for this blog, Happy querying!