Disclosure: This post includes affiliate links; I may receive compensation if you purchase products or services from the different links provided in this article.
Hello devs, SQL questions are quite common on programming interviews and one of the popular SQL question is "WHERE vs HAVING clause"? When it comes to filtering records in SQL query, there are two main options, either by using WHERE clause or by using HAVING clause.
While both WHERE and HAVING are used for filtering rows, condition in WHERE clause is applied before grouping of data and condition on HAVING is applied after grouping
I mean, the main difference between them is that you can use WHERE if you want to filter data before grouping, I mean before you group records using GROUP BY clause and use HAVING when you want to filter groups.
This distinction is made by Query Engine on most of popular database like MySQL, Microsoft SQL Server, Oracle, and PostgreSQL
For example,
SELECT *
FROM BOOK
WHERE author="Joshua Bloch"
will only show books where author is "Joshua Bloch", here we have used WHERE clause because there is no grouping.
In case, we need grouping like authors with number of books we can use group by and having clause together and it will print only authors which have more than one book.
SELECT author, count(*) as NumberOfBooks
FROM BOOK
GROUP BY author
HAVING NumberOfBooks > 1
You can also use WHERE and HAVING clause together in one query and in that case WHERE clause will filter before grouping and HAVING clause will filter after grouping as shown in following example:
SELECT author, count(*) as NumberOfBooks
FROM BOOK
WHERE title like '%SQL%'
GROUP BY author\
HAVING NumberOfBooks > 1
This will only print author which have multiple books with title 'SQL in them.
By the way, if you are new to SQL, then you can also use websites like Udemy, Coursera, Educative, ZTM Academy, freeCodeCamp, and VladMihalcea' SQL course to learn SQL in depth.
Difference between WHERE and HAVING clause in SQL
Now that you know what is WHERE and HAVING clause in SQL and what the do, here are more useful difference between WHERE and HAVING clause in SQL :
1. WHERE clause can be used with SELECT, UPDATE and DELETE statements and clauses but HAVING clause can only be used with SELECT statements.
e.g.
SELECT * FROM Employee WHERE EmployeeId=3
This query will print details of employee with id = 3.
Similarly,
SELECT EmployeeName, COUNT(EmployeeName) AS NumberOfEmployee
FROM Employee
HAVING COUNT(EmployeeName) > 2;
this query will print duplicate employees from table.
2. We can't use aggregate functions in the where clause unless it is in a sub query contained in a HAVING clause whereas we can use aggregate function in Having clause. We can use column name in Having clause but the column must be contained in the group by clause.
3. WHERE clause is used on the individual records whereas Having Clause in conjunction with Group By Clause work on the record sets ( group of records ).
And, if you need more SQL questions like this then you can also see, Grokking the SQL Interview book which covers key topics for SQL interviews
That's all about difference between WHERE and HAVING clause in SQL. This is one of the important SQL questions and if you are preparing for Java developer interview, you should know the answer of this question.
While its a very common concept and we used it on daily basis, not many people can answer it correctly on interview.
Mentioning about keywords like filtering and before and after grouping is key here.
By the way, this is also a common SQL question on Java interviews and if you are preparing for Java interviews, you can also see my earlier articles like 35 Java Questions, 15 Spring Framework Questions and 6 System Design Problem to prepare other topics.
All the best !!
Top comments (1)
Thank you, good information 🙂