Adam Sh Adam Sh - 6 months ago 20
SQL Question

SQL - having VS where

I have two table:

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.

Answer

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.