WHERE and HAVING in SQL
Filtering data is a crucial part of SQL queries, and two important clauses used for this purpose are WHERE and HAVING. Both of these clauses help in filtering records based on specified conditions, but they serve different purposes in SQL. The WHERE clause is used to filter individual rows before any grouping occurs, while the HAVING clause is used to filter grouped records after an aggregate function has been applied. This distinction is one of the most commonly asked SQL interview questions.
Before diving into the key differences, let’s explore each clause in detail.
Complete Python Course with Advance topics:-Click Here
SQL Tutorial :-Click Here
WHERE Clause
The WHERE clause in SQL is used with SELECT, INSERT, UPDATE, and DELETE queries to filter records based on specified conditions. It retrieves only those rows that meet the given condition before any grouping takes place. This clause can be used with logical operators such as AND, OR, and NOT and supports various comparison operators like <, <=, >, >=, =, 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 data:
| emp_id | emp_name | working_hours |
|---|---|---|
| 101 | Arjun | 10 |
| 102 | Meera | 8 |
| 103 | Rohan | 12 |
| 104 | Simran | 7 |
If we want to retrieve details of employees who have worked more than 9 hours, we can use:
SELECT * FROM employees
WHERE working_hours > 9;
Output:
| emp_id | emp_name | working_hours |
|---|---|---|
| 101 | Arjun | 10 |
| 103 | Rohan | 12 |
HAVING Clause
The HAVING clause in SQL is used in conjunction with the GROUP BY clause to filter grouped records based on aggregate functions. Unlike WHERE, which filters individual rows, HAVING filters groups after aggregation.
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:
Consider the same employees table. If we want to find employees whose total working hours exceed 15 across multiple days, we use:
SELECT emp_name, SUM(working_hours) AS total_hours
FROM employees
GROUP BY emp_name
HAVING SUM(working_hours) > 15;
Output:
| emp_name | total_hours |
|---|---|
| Arjun | 18 |
| Rohan | 20 |
Key Differences Between WHERE and HAVING
| Feature | WHERE Clause | HAVING Clause |
|---|---|---|
| Functionality | Filters individual rows before grouping. | Filters groups after aggregation. |
| Use with Aggregate Functions | Cannot be used with aggregate functions. | Can be used with aggregate functions like SUM, COUNT, etc. |
| Execution Order | Applied before GROUP BY. | Applied after GROUP BY. |
| Efficiency | Faster as it works on raw data. | Slower as it works on grouped data. |
| Usage | Can be used with SELECT, UPDATE, DELETE statements. | Used only with SELECT statements. |
Combining WHERE and HAVING
We can use both WHERE and HAVING in a single query. The WHERE clause filters raw data, and after grouping, the HAVING clause filters aggregated results.
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
Complete Advance AI topics:- CLICK HERE
Conclusion
Both WHERE and HAVING are used for filtering in SQL but operate at different stages of query execution. WHERE filters individual rows before grouping, while HAVING filters grouped results after aggregation. Understanding these differences is essential for writing efficient SQL queries and performing accurate data analysis.
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
