What is the difference between a HAVING CLAUSE and WHERE CLAUSE?

There are several questions in my mind until i am not giving the answer of this question like -

• 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)?

But now i have giving all my answers. Assume we have a table like below :

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 like below :

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`>5; -- Get 5 rows
SELECT `value` v FROM `table` HAVING `value`>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`>5;

 
Above Query will return an Error #1054 - Unknown column 'v' in 'where clause'

SELECT `value` v FROM `table` HAVING `v`>5; -- Get 5 rows

Main Difference : 

• WHERE clause requires a condition to be a column in a table, but HAVING clause can use either column or alias.
• This is because WHERE clause filters data before select, but HAVING clause filters data after select.

Try EXPLAIN to see the key difference:

EXPLAIN SELECT `value` v FROM `table` WHERE `value`>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`>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.
 
If you like this post don't forgot to leave a comment.

Chears :) Happy Coding..