Difference between WHERE and HAVING clause in SQL?

The main difference between WHERE and HAVING clause comes when used together with 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 WHERE clause because its one of the most common clause in SQL along with SELECT and used to specify filtering criterion or condition. You can even use WHERE clause without HAVING or GROUP BY, as you have seen it many times. 

On the other hand, HAVING can only be used if grouping has been performed using GROUP BY clause in the SQL query. Another worth noting thing about WHERE and HAVING clause is that WHERE clause cannot contain aggregate function like COUNT(), SUM(), MAX(), MIN(), etc but  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 The Complete SQL Bootcamp course on Udemy. One of the best course to learn SQL for both beginners and intermediate developers.




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 any column from a table in WHERE clause but you can only use columns which are not grouped or aggregated.

3) If you use HAVING clause without group by, it can also refer any column but the index will not be used as opposed to 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 table having id = 1

4) You can use an aggregate function to filter rows with HAVING clause. Because 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 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 for per group. You can further see the Introduction to SQL course on Pluralsight to learn more about.

Difference between WHERE and HAVING clause in SQL?


If you don't know, Pluralsight is membership based website and you need to be a member to join this course but you can join the Pluralsight in just $29 per month or $299 for an annual subscription.

 This will give you access to Pluralsight library of more than 5000+ online courses on not only SQL but also on other technology, which is great for Programmers always looking to learn and upgrade themselves.

They also provide a 10-day free trial without any obligation which is a good way to access this course for free and also to check the quality of Pluralsight online courses.  Another best thing to share with you guys is that they are now giving $100 on their annual subscription and renewals. So, if you want to join Pluralsight this is probably the best time to join.




When to use WHERE and HAVING clause?

Though both are used to exclude rows from the result set, you should use 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 function like count, sum, avg, min, and max.

If filtering can be done without aggregate function then you must do it on WHERE clause because it improves performance because counting and sorting will be done on a much smaller set. If you filter 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 e.g. 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 AGGREGATE function along with HAVING clause for filtering.

Further Learning
Introduction to SQL
The Complete SQL Bootcamp
SQL for Newbs: Data Analysis for Beginners

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.

No comments:

Post a Comment