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 · Accepted 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

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

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

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

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

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

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

[mysql] WHERE vs HAVING

Examples related to mysql

Examples related to sql

Examples related to where-clause

Examples related to having-clause