mysql - SQL, HAVING clause explained -

can please explain how use having clause, dumb down as possible. looked @ texbook, w3schools, , youtube still cant wrap mind around this. don't know if im on thinking need learn this.

having used filter on aggregations in group by.

for example, check duplicate names:

select name usernames group name having count(*) > 1 

assume have 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 columns both id , value 1 10:

insert `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 following 2 queries:

select `value` v `table` `value`>5; -- 5 rows select `value` v `table` having `value`>5; -- 5 rows 

you same results, can see having clause can work without group clause.

here's difference:

select `value` v `table` `v`>5; 

error #1054 - unknown column 'v' in 'where clause'

select `value` v `table` having `v`>5; -- 5 rows 

where clause requires condition column in table, having clause can use both column , alias.

this because clause filters data before select, having clause filters data after select.

so put conditions in clause more effective if have many many rows in table.

try explain see key difference:

explain select value v table value>5;

+----+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+ | id | select_type | table | type  | possible_keys | key   | key_len | ref  | rows |                    | +----+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+ |  1 | simple      | table | range | value         | value | 4       | null |    5 | using where; using index | +----+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+ 

explain select value v table having value>5;

+----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+ | id | select_type | table | type  | possible_keys | key   | key_len | ref  | rows |       | +----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+ |  1 | simple      | table | index | null          | value | 4       | null |   10 | using index | +----+-------------+-------+-------+---------------+-------+---------+------+------+-------------+ 

you can see either or having uses index, rows different.