SQL - HAVING vs. WHERE
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.
WHERE clause can be used with SELECT, INSERT, and UPDATE statements, whereas HAVING can be used only with SELECT statement.
WHERE filters rows before aggregation (GROUP BY), whereas HAVING filter groups after aggregations are performed.
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
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.