HAVING vs. WHERE in SQL

Problem

I’ve got two tables for you:

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

I’m looking for the professor with the greatest specialized knowledge. This does not work when I attempt it:

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);

However, when I try it, 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 explanation for this? Thanks.

Asked by Adam Sh

Solution #1

The WHERE clause adds a condition to individual rows; the HAVING clause adds a condition to aggregations, or outcomes of selection where a single result, such as count, average, min, max, or total, is derived from numerous rows. Because your query requires a second type of condition (a condition on an aggregation), HAVING works.

Use WHERE before GROUP BY and HAVING after GROUP BY as a rule of thumb. It’s a simple rule, yet it works in more than 90% of the circumstances.

While you’re about it, you might as well rewrite your query using the ANSI 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)

WHERE would be removed as a theta join condition as a result of this.

Answered by Sergey Kalinichenko

Solution #2

We must first determine the sequence in which Clauses are executed, i.e. FROM > WHERE > GROUP BY > HAVING > DISTINCT > SELECT > ORDER BY. Because the WHERE Clause executes before the GROUP BY Clause, records can’t be filtered by using WHERE on GROUP BY records.

“The HAVING clause is similar to the WHERE clause, but it is used on grouped records.”

The WHERE clause retrieves records based on a condition, then the GROUP BY clause groups them, and finally the HAVING clause retrieves group records depending on the having condition.

Answered by Pardhu

Solution #3

HAVING works with aggregates. You can’t use COUNT in a WHERE clause because it’s an aggregate function.

Here’s some information about aggregate functions from MSDN.

Answered by Daniel Mann

Solution #4

Source

Answered by Venkata Krishna Reddy

Solution #5

I couldn’t find an example of both in a single query. As a result, this example may be useful.

  /**
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

s first filters the table by companyId, then groups it (by nation and city), and finally narrows it down to only Mexican city aggregations. Although the companyId was not required for the aggregation, we were able to filter out only the items we wanted before applying GROUP BY by using WHERE.

Answered by Nhan

Post is based on https://stackoverflow.com/questions/9253244/sql-having-vs-where