Difference Between WHERE and HAVING in SQL

WHERE and HAVING in SQL

Filtering data is an important part of writing SQL queries, and two commonly used clauses for this purpose are WHERE and HAVING. Although both are used to filter data based on conditions, they work at different stages of query processing.

The WHERE clause filters individual rows before grouping takes place, whereas the HAVING clause filters groups after an aggregate function has been applied. Understanding this difference is also a common topic in SQL interviews.

Before looking at their differences, let’s understand each clause separately.

Complete Advance AI Topics: Click Here
SQL Tutorial: Click Here

WHERE Clause

The WHERE clause is used with SELECT, INSERT, UPDATE, and DELETE queries to filter records according to a specified condition.

It returns only the rows that satisfy the condition before any grouping occurs. The clause can be combined with logical operators such as AND, OR, and NOT, along with comparison operators such as <, <=, >, >=, =, and <>.

Syntax of WHERE Clause

SELECT column_list
FROM table_name
WHERE condition
GROUP BY column_list;

Example of WHERE Clause

Consider an employees table containing the following records:

To retrieve employees who have worked for more than 9 hours, use the following query:

SELECT * FROM employees
WHERE working_hours > 9;

Output:

HAVING Clause

The HAVING clause is generally used together with GROUP BY to filter grouped records based on aggregate functions.

Unlike WHERE, which works on individual rows, HAVING filters the groups after aggregation has taken place.

Syntax of HAVING Clause

SELECT column_list,
aggregate_function(expression)
FROM table_name
WHERE condition
GROUP BY column_list
HAVING condition;

Example of HAVING Clause

Using the same employees table, suppose we want to find employees whose total working hours exceed 15 hours across multiple days.

SELECT emp_name, SUM(working_hours) AS total_hours
FROM employees
GROUP BY emp_name
HAVING SUM(working_hours) > 15;

Output:

Key Differences Between WHERE and HAVING

Combining WHERE and HAVING

Both WHERE and HAVING can be used in the same SQL query. In this situation, WHERE first filters the raw records, and after the data is grouped, HAVING filters the resulting groups.

Example

SELECT emp_name, SUM(working_hours) AS total_hours
FROM employees
WHERE working_hours > 6
GROUP BY emp_name
HAVING SUM(working_hours) > 15;

Download New Real Time Projects :- Click here

Conclusion

Both WHERE and HAVING are used to filter information in SQL, but they work at different stages of query execution.

WHERE filters individual rows before grouping, while HAVING filters grouped results after aggregation. Understanding this distinction is essential for writing efficient SQL queries and performing accurate data analysis.

SEO Keywords

difference between where and having clause with example, difference between where and having clause class 12, difference between where and having clause w3schools, difference between having and group by, where and having in sql in same query, where and having in sql performance, where and having in sql, difference between where and having in sql, when to use where and having in sql, difference between where and having in sql server, where and having in sql query, aggregate functions in sql, sql interview questions, difference between where and having in sql with example, difference between where and having in sql server, difference between where and having in sql oracle