WHERE vs HAVING

Question

Why do you need to place columns you create yourself  for example select 1 as  number   after HAVING and not WHERE in MySQL   And are there any downsides instead of doing WHERE 1  writing the whole definition instead of a column name

User · Answer

These 2 will be feel same as first as both are used to say about a condition to filter data. Though we can use ‘having’ in place of ‘where’ in any case, there are instances when we can’t use ‘where’ instead of ‘having’. This is because in a select query, ‘where’ filters data before ‘select’ while ‘having’ filter data after ‘select’. So, when we use alias names that are not actually in the database, ‘where’ can’t identify them but ‘having’ can.

Ex: let the table Student contain student_id,name, birthday,address.Assume birthday is of type date.

SELECT * FROM Student WHERE YEAR(birthday)>1993; /*this will work as birthday is in database.if we use having in place of where too this will work*/

SELECT student_id,(YEAR(CurDate())-YEAR(birthday)) AS Age FROM Student HAVING Age>20; 
/*this will not work if we use ‘where’ here, ‘where’ don’t know about age as age is defined in select part.*/

User · Answer

Why is it that you need to place columns you create yourself  for example  select 1 as number   after HAVING and not WHERE in MySQL    WHERE is applied before GROUP BY  HAVING is applied after  and can filter on aggregates    In general  you can reference aliases in neither of these clauses  but MySQL allows referencing SELECT level aliases in GROUP BY  ORDER BY and HAVING      And are there any downsides instead of doing  WHERE 1   writing the whole definition instead of a column name    If your calculated expression does not contain any aggregates  putting it into the WHERE clause will most probably be more efficient

User · Answer

Having is only used with aggregation but where with non aggregation statements If you have where word put it before aggregation  group by

User · Answer

WHERE filters before data is grouped  and HAVING filters after data is grouped  This is an important distinction  rows that are   eliminated by a WHERE clause will not be included in the group  This   could change the calculated values which  in turn  as a result  could affect which   groups are filtered based on the use of those values in the HAVING   clause    And continues      HAVING is so similar to WHERE that most DBMSs treat them as the same   thing if no GROUP BY is specified  Nevertheless  you should make that   distinction yourself  Use HAVING only in conjunction with GROUP BY   clauses  Use WHERE for standard row-level filtering    Excerpt From   Forta  Ben     Sams Teach Yourself SQL in 10 Minutes  5th  Edition   Sams Teach Yourself

User · Answer

HAVING is used to filter on aggregations in your GROUP BY   For example  to check for duplicate names   SELECT Name FROM Usernames GROUP BY Name HAVING COUNT     gt  1

User · Answer

All other answers on this question didn t hit upon the key point   Assume we have a table   CREATE TABLE  table      id  int 10  unsigned NOT NULL AUTO INCREMENT    value  int 10  unsigned NOT NULL   PRIMARY KEY   id     KEY  value    value     ENGINE InnoDB DEFAULT CHARSET utf8   And have 10 rows with both id and value from 1 to 10   INSERT INTO  table   id    value   VALUES  1  1   2  2   3  3   4  4   5  5   6  6   7  7   8  8   9  9   10  10     Try the following 2 queries   SELECT  value  v FROM  table  WHERE  value  gt 5  -- Get 5 rows SELECT  value  v FROM  table  HAVING  value  gt 5  -- Get 5 rows   You will get exactly the same results  you can see the HAVING clause can work without GROUP BY clause     Here s the difference   SELECT  value  v FROM  table  WHERE  v  gt 5    Error  1054 - Unknown column  v  in  where clause   SELECT  value  v FROM  table  HAVING  v  gt 5  -- Get 5 rows   WHERE clause allows a condition to use any table column  but it cannot use aliases or aggregate functions  HAVING clause allows a condition to use a selected     column  alias or an aggregate function   This is because WHERE clause filters data before select  but HAVING clause filters resulting data after select   So put the conditions in WHERE clause will be more efficient if you have many many rows in a table   Try EXPLAIN to see the key difference   EXPLAIN SELECT  value  v FROM  table  WHERE  value  gt 5   ---- ------------- ------- ------- --------------- ------- --------- ------ ------ --------------------------    id   select type   table   type    possible keys   key     key len   ref    rows   Extra                       ---- ------------- ------- ------- --------------- ------- --------- ------ ------ --------------------------     1   SIMPLE        table   range   value           value   4         NULL      5   Using where  Using index    ---- ------------- ------- ------- --------------- ------- --------- ------ ------ --------------------------   EXPLAIN SELECT  value  v FROM  table  having  value  gt 5   ---- ------------- ------- ------- --------------- ------- --------- ------ ------ -------------    id   select type   table   type    possible keys   key     key len   ref    rows   Extra          ---- ------------- ------- ------- --------------- ------- --------- ------ ------ -------------     1   SIMPLE        table   index   NULL            value   4         NULL     10   Using index    ---- ------------- ------- ------- --------------- ------- --------- ------ ------ -------------    You can see either WHERE or HAVING uses index  but the rows are different

User · Answer

The main difference is that WHERE cannot be used on grouped item  such as SUM number   whereas HAVING can    The reason is the WHERE is done before the grouping and HAVING is done after the grouping is done

[mysql] WHERE vs HAVING

Examples related to mysql

Examples related to sql

Examples related to where-clause

Examples related to having-clause