What is the difference between HAVING and WHERE in SQL?
HAVING specifies a search condition for a
group or an aggregate function used in SELECT statement.
Source
Difference between HAVING and WHERE Clause
Functionally, the two are equivalent.
The WHERE
clause is saying:
Filter the data and then aggregate the results.
The HAVING
clause is saying:
Aggregate the data and then filter the results.
Both return the same result, because the filtering is on the columns used for aggregation. Usually, HAVING
uses aggregation functions; these are not allowed in the WHERE
.
In general, the WHERE
clause is going to be faster, because less data is being aggregated. You should use WHERE
in this case.
Difference between HAVING and WHERE in SQL
The simple way to think about it is to consider the order in which the steps are applied.
Step 1: Where clause filters data
Step 2: Group by is implemented (SUM / MAX / MIN / ETC)
Step 3: Having clause filters the results
So in your 2 examples:
SELECT agentId, SUM(quantity) total_sales
FROM sales s, houses h
WHERE s.houseId = h.houseId AND h.type = "condo"
GROUP BY agentId
ORDER BY total_sales;
Step 1: Filter by HouseId and Condo
Step 2: Add up the results
(number of houses that match the houseid and condo)
SELECT agentId, SUM(quantity) total_sales
FROM sales s, houses h
GROUP BY agentId
HAVING s.houseId = h.houseId AND h.type = "condo"
ORDER BY total_sales;
Step 1: No Filter
Step 2: Add up quantity of all houses
Step 3: Filter the results by houseid and condo.
Hopefully this clears up what is happening.
The easiest way to decide which you should use is:
- Use WHERE to filter the data
- Use HAVING to filter the results of an aggregation (SUM / MAX / MIN / ETC)
Difference between HAVING and WHERE clause in SQL
where
filters on the select ... from
having
filters on the aggregate results from the group by ...
So, looking at your example again:
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value
Here, WHERE column_name operator value
says "Return results for table_name where
'column_name operator value' is true".
Only after all the results from these conditions are found, it groups by column_name.
Then HAVING aggregate_function(column_name) operator value
says "For the resulting aggregate groups, run 'aggregate_function(column_name)' and return only results where 'aggregate_function(column_name) operator value' is true."
SQL - HAVING vs. WHERE
WHERE
clause introduces a condition on individual rows; HAVING
clause introduces a condition on aggregations, i.e. results of selection where a single result, such as count, average, min, max, or sum, has been produced from multiple rows. Your query calls for a second kind of condition (i.e. a condition on an aggregation) hence HAVING
works correctly.
As a rule of thumb, use WHERE
before GROUP BY
and HAVING
after GROUP BY
. It is a rather primitive rule, but it is useful in more than 90% of the cases.
While you're at it, you may want to re-write your query using ANSI version of the join:
SELECT L.LectID, Fname, Lname
FROM Lecturers L
JOIN Lecturers_Specialization S ON L.LectID=S.LectID
GROUP BY L.LectID, Fname, Lname
HAVING COUNT(S.Expertise)>=ALL
(SELECT COUNT(Expertise) FROM Lecturers_Specialization GROUP BY LectID)
This would eliminate WHERE
that was used as a theta join condition.
MySql - HAVING vs WHERE
Difference between the having and where clause in sql is that the where clause can not be used with aggregates, but the having clause can. One way to think of it is that the having clause is an additional filter to the where clause.
Which is better : click
Related Topics
When Do I Need to Use a Semicolon VS a Slash in Oracle Sql
MySQL Query Finding Values in a Comma Separated String
How to Delete Duplicate Rows in SQL Server
Best Way to Do Multi-Row Insert in Oracle
Passing a Varchar Full of Comma Delimited Values to a SQL Server in Function
How to Implement a Many-To-Many Relationship in Postgresql
Why Does Oracle 9I Treat an Empty String as Null
MySQL - Get Row Number on Select
Using Group by on Multiple Columns
How to Query SQL For a Latest Record Date For Each User
Combining "Like" and "In" For SQL Server
How to Use an Insert Statement'S Output Clause to Get the Identity Value
MySQL Fails On: MySQL "Error 1524 (Hy000): Plugin 'Auth_Socket' Is Not Loaded"