SQL - HAVING vs. WHERE

I have the following two tables:

1. Lecturers (LectID, Fname, Lname, degree). 2. Lecturers_Specialization (LectID, Expertise). 

I want to find the lecturer with the most Specialization. When I try this, it is not working:

SELECT L.LectID, Fname, Lname FROM Lecturers L, Lecturers_Specialization S WHERE L.LectID = S.LectID AND COUNT(S.Expertise) >= ALL (SELECT COUNT(Expertise) FROM Lecturers_Specialization GROUP BY LectID); 

But when I try this, it works:

SELECT L.LectID, Fname, Lname FROM Lecturers L, Lecturers_Specialization S WHERE L.LectID = S.LectID GROUP BY L.LectID, Fname, Lname HAVING COUNT(S.Expertise) >= ALL (SELECT COUNT(Expertise) FROM Lecturers_Specialization GROUP BY LectID); 

What is the reason? Thanks.

9 Answers

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.

First we should know the order of execution of Clauses i.e FROM > WHERE > GROUP BY > HAVING > DISTINCT > SELECT > ORDER BY. Since WHERE Clause gets executed before GROUP BY Clause the records cannot be filtered by applying WHERE to a GROUP BY applied records.

"HAVING is same as the WHERE clause but is applied on grouped records".

first the WHERE clause fetches the records based on the condition then the GROUP BY clause groups them accordingly and then the HAVING clause fetches the group records based on the having condition.

HAVING operates on aggregates. Since COUNT is an aggregate function, you can't use it in a WHERE clause.

Here's some reading from MSDN on aggregate functions.

1. WHERE clause can be used with SELECT, INSERT, and UPDATE statements, whereas HAVING can be used only with SELECT statement.

2. WHERE filters rows before aggregation (GROUP BY), whereas HAVING filter groups after aggregations are performed.

3. Aggregate function cannot be used in WHERE clause unless it is in a subquery contained in HAVING clause, whereas aggregate functions can be used in HAVING clause.

Source

Didn't see an example of both in one query. So this example might help.

 /** INTERNATIONAL_ORDERS - table of orders by company by location by day companyId, country, city, total, date **/ SELECT country, city, sum(total) totalCityOrders FROM INTERNATIONAL_ORDERS with (nolock) WHERE companyId = 884501253109 GROUP BY country, city HAVING country = 'MX' ORDER BY sum(total) DESC 

This filters the table first by the companyId, then groups it (by country and city) and additionally filters it down to just city aggregations of Mexico. The companyId was not needed in the aggregation but we were able to use WHERE to filter out just the rows we wanted before using GROUP BY.

You can not use where clause with aggregate functions because where fetch records on the basis of condition, it goes into table record by record and then fetch record on the basis of condition we have give. So that time we can not where clause. While having clause works on the resultSet which we finally get after running a query.

Example query:

select empName, sum(Bonus) from employees order by empName having sum(Bonus) > 5000; 

This will store the resultSet in a temporary memory, then having clause will perform its work. So we can easily use aggregate functions here.

1. We can use aggregate function with HAVING clause not by WHERE clause e.g. min,max,avg.

2. WHERE clause eliminates the record tuple by tuple HAVING clause eliminates entire group from the collection of group

Mostly HAVING is used when you have groups of data and WHERE is used when you have data in rows.

WHERE clause is used to eliminate the tuples in a relation,and HAVING clause is used to eliminate the groups in a relation.

HAVING clause is used for aggregate functions such as MIN,MAX,COUNT,SUM .But always use GROUP BY clause before HAVING clause to minimize the error.

Both WHERE and HAVING are used to filter data. In case of a WHERE statement, data filtering happens before you pull the data for operation.

SELECT name, age FROM employees WHERE age > 30; 

Here the WHERE clause filters rows before the SELECT operation is performed.

SELECT department, avg(age) avg_age FROM employees GROUP BY department HAVING avg_age> 35; 

HAVING filters the data after the SELECT operation is performed. Here the operation of computing (aggregation) is done first and then a filter is applied to the result using a HAVING clause.