SQL HAVING Vs WHERE Statement

When the WHERE and HAVING clauses are combined with the GROUP BY clause, the fundamental difference between them becomes apparent.

WHERE is used to filter rows prior to grouping while HAVING is used to exclude data after grouping in this scenario.

This is the most essential distinction, and remembering it will assist you in writing better SQL queries.

This is also one of the most crucial SQL principles to grasp, not only for interviews but also for everyday use.

I'm sure you've used the WHERE Clause because, along with SELECT , it's one of the most commonly used clauses in SQL for specifying filtering criteria or conditions.

SQL HAVING Statement

When used in conjunction with the GROUP BY clause in MySQL, the HAVING clause allows us to set conditions that select which group results show in the result.

Only those values from the groups in the final result that meet specified criteria are returned.

During selection, we can also combine the WHERE and HAVING clauses.

In this scenario, the WHERE clause filters individual rows first, then groups them, conducts aggregate calculations, and then filters the groups with the HAVING clause.

The GROUP BY clause creates groups, and this clause adds conditions to them.

When the SQL statement does not use the GROUP BY keyword, it behaves as the WHERE clause.

SUM, MIN, MAX, AVG, and COUNT are aggregate (group) functions that can only be used with two clauses: SELECT and HAVING.

Example: Let us consider Employee table mentioned above and apply having clause on it:

Mysql,Sqlserver,Sqlite

id	name	dept	salary
1	Harish Karthik	Programming	14000
2	Balan	Administration	33000
3	Ramanathan	Programming	12500
4	Ranjani Mai	Web Design	33500

SELECT Dept,sum(Salary) as Salary
FROM EmployeeSimple
HAVING sum(Salary>=5000)

Ouput:

dept	salary
Programming	93000

SQL WHERE Statement

The WHERE clause in MySQL is used to filter data from a table or relation in SELECT, INSERT, UPDATE, and DELETE queries.

When using the JOIN clause to retrieve records from a single table or multiple tables, it describes a specific condition.

It returns the specific value from the table if the provided condition is met.The WHERE clause applies conditions to the columns that have been chosen.

The logical connectives AND, OR, and NOT can also be implemented in MySQL's WHERE clause.

They're known as the Boolean condition, and they have to be true in order to access data. The comparison operators, such as,=, >, >=, =, and >, are used as operands in logical connectives expressions.

Strings and arithmetic expressions are commonly compared using comparison operators.

Example: If we wish to acquire the details of employees that who get salary above 30000 , we can utilise the following statement:

id	name	gender	city	dept	salary
1	Rishi Keshan	Male	Hyderbhad	Security	33000
2	Siva Kumar	Male	Hyderbhad	Web Design	42000
3	Varshini Kutty	Female	Hyderbhad	Administration	5000
4	Ranjani Mai	Female	Pune	Security	30000

SELECT * FROM EmployeeAdvance
WHERE  Salary >30000;

Output: The output will be

id	name	gender	city	dept	salary
1	Rishi Keshan	Male	Hyderbhad	Security	33000
2	Siva Kumar	Male	Hyderbhad	Web Design	42000

SQL Difference Between HAVING And WHERE Statement

There are 11 main distinguish in having Vs where clause :-

WHERE	HAVING
It's used to do individual row filtration.	Instead of filtering one row at a time, the HAVING clause filters groups.
Row operations are used to implement it.	Column operations are used to implement it.
With single row functions like `UPPER`, `LOWER`, and so on, the `WHERE` Clause is used.	`EXISTING` Clause is used with multi-row functions such as `SUM`, COUNT, and so on.
The WHERE clause retrieves specific data from certain rows based on the condition supplied.	The HAVING clause retrieves all of the data first. It then divides them into groups based on the condition.
Because the WHERE clause is designed to filter individual rows, we can't use it with aggregate functions.	Because it is used to filter groups, HAVING can be used with aggregate functions.
The WHERE clause is used as a pre-filter.	The HAVING clause is used as a post-filter.
In a `SELECT` query, the WHERE and HAVING clauses can be combined. The WHERE clause is used initially to filter specific rows in this scenario.	The rows are then grouped, aggregate computations are performed, and the groupings are finally filtered using the HAVING clause.
The WHERE clause can be used with `SELECT`, `UPDATE`, and `DELETE` queries.	Only the `SELECT` statement can employ the HAVING clause.
WHERE comes before GROUP BY, which means WHERE clause filter rows before performing aggregate calculations.	Because HAVING appears after GROUP BY, the HAVING clause selects data after aggregate calculations are completed.
In terms of efficiency, WHERE is faster than HAVING and should be avoided wherever possible.	As a result, HAVING is less efficient than WHERE and should be avoided wherever possible.
WHERE will utilise Index; for example, the results of the following two searches will be identical, but WHERE will use Index. SELECT * FROM Course WHERE Id = 101;	HAVING does not employ indexes; for example, if two queries return the same result, HAVING will perform a table scan. SELECT * FROM Course HAVING Id = 102;

All Tutorials