SQL - HAVING vs WHERE

Question

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   gt   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   gt   ALL  SELECT   COUNT Expertise  FROM Lecturers Specialization GROUP BY LectID      What is the reason  Thanks

User · Answer

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

User · Answer

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

User · 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  gt  ALL  SELECT COUNT Expertise  FROM Lecturers Specialization GROUP BY LectID    This would eliminate WHERE that was used as a theta join condition

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   gt  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

[sql] SQL - HAVING vs. WHERE

Examples related to sql

Examples related to where-clause

Examples related to having