Difference between WHERE and HAVING clause in SQL? Example

The main difference between the WHERE and HAVING clauses comes when used together with the GROUP BY clause. In that case, WHERE is used to filter rows before grouping, and HAVING is used to exclude records after grouping. This is the most important difference, and if you remember this, it will help you write better SQL queries. This is also one of the important SQL concepts to understand, not just from an interview perspective but also from a day-to-day use perspective. I am sure you have used the WHERE clause because it's one of the most common clauses in SQL along with SELECT and used to specify filtering criteria or conditions.

You can even use the WHERE clause without HAVING or GROUP BY, as you have seen many times. On the other hand, HAVING can only be used if grouping has been performed using the GROUP BY clause in the SQL query. 

Another worth noting thing about the WHERE and HAVING clause is that the WHERE clause cannot contain aggregate functions like COUNT(), SUM(), MAX(), MIN(), etc but the HAVING clause may contain aggregate functions.

Another worth noting the difference between WHERE and HAVING clause is that WHERE is used to impose filtering criterion on a SELECT, UPDATE, DELETE statement as well as single row function and used before group by clause but HAVING is always used after group by clause.

If you are starting with SQL, then these are some of the fundamentals you need to learn, and a good course can help you a lot. If you need a recommendation, I suggest you join any of these best SQL and database courses online. This list contains the best courses from Udmey, Coursera, Pluralsight, and other websites. 

Difference between WHERE vs. HAVING in SQL

1. WHERE clause is processed right after FROM clause in the logical order of query processing, which means it is processed before GROUP BY clause while HAVING clause is executed after groups are created.

2. If used in GROUP BY, You can refer to any column from a table in the WHERE clause, but you can only use columns that are not grouped or aggregated.

3. If you use the HAVING clause without group by, it can also refer to any column, but the index will not be used as opposed to the WHERE clause. For example, the following have the same result set, however "where" will use the id index and having will do a table scan

select * from table where id = 1
select * from the table having id = 1

4. You can use an aggregate function to filter rows with the HAVING clause. Because the HAVING clause is processed after the rows have been grouped, you can refer to an aggregate function in the logical expression. For example, the following query will display only courses which have more than 10 students :

SELECT Course, COUNT(Course) as NumOfStudent 
FROM Training 
GROUP BY Course 
HAVING COUNT(Course)> 10

5. Another key difference between WHERE and HAVING clause is that WHERE will use Index and HAVING will not; for example following two queries will produce an identical result, but WHERE will use Index and HAVING will do a table scan

SELECT * FROM Course WHERE Id = 101;
SELECT * FROM Course HAVING Id = 102;

6. Since the WHERE clause is evaluated before groups are formed, it evaluates for per row. On the other hand, the HAVING clause is evaluated after groups are formed; hence it evaluates per group. You can further see these free SQL online courses to learn more about it.

When to use WHERE and HAVING clauses?

Though both are used to exclude rows from the result set, you should use the WHERE clause to filter rows before grouping and use the HAVING clause to filter rows after grouping. In other words, WHERE can be used to filter on table columns while HAVING can be used to filter on aggregate functions like count, sum, avg, min, and max.

If filtering can be done without aggregate function then you must do it on the WHERE clause because it improves performance because counting and sorting will be done on a much smaller set. If you filter the same rows after grouping, you unnecessarily bear the cost of sorting, which is not used.

For example, the following statement is syntactically correct and produce the same result, but the second one is more efficient than the first one because it filters rows before grouping :

SELECT Job, City, State, Count(Employee) from ... HAVING...
SELECT ..... from .. WHERE ..

That's all about the difference between WHERE and HAVING clause in SQL. These differences are valid for almost all major databases like MySQL, Oracle, SQL Server, and PostgreSQL. Just remember that WHERE is used to filter rows before grouping while HAVING is used to filter rows after grouping. You can also use the AGGREGATE function along with the HAVING clause for filtering.


Other SQL Articles and Interview Questions you may like

• How to join more than two tables in a single query (article)
• 5 Websites to learn SQL for FREE (websites)
• 5 Free Courses to Learn MySQL database (courses)
• 10 SQL queries from Interviews (queries)
• 5 Books to Learn SQL Better (books)
• Difference between truncate and delete in SQL (answer)
• Difference between row_number and rank in SQL? (answer)
• 5 Free Courses to learn Database and SQL (courses)
• Difference between UNION and UNION ALL in SQL? (answer)
• Top 5 SQL books for Advanced Programmers (books)
• Difference between SQL, T-SQL, and PL/SQL? (answer)
• Top 5 Online Courses to Learn SQL and Database (courses)

Thanks for reading this article so far. If you like this SQL article then please share with your friends and colleagues. If you have any questions or feedback then please drop a note.