mysql  having  clause

the MySQL having clause is used in the select statement to specify filter conditions for group of rows or aggregates.

the having clause in MySQl can be used with the group by clause.

the having clause was added to mysql because the where keyword could not be used with aggregate functions.

mysql group by syntax

         select   column_name, aggregate_function(expression)

                      from   table_name
                      
                      where  condition
                      
                      group by  column_name
                      
                      having    aggregate_function(column_name)  operator value;

demo database

below is a selection from the "users" table:

useridfirstnamelastnamesalarycity
1sandeepkumar10000india
2ramkumar20000usa
3shyamdhaka30000jaipur
4ankitnehra30000uae
5shyamdhaka40000jaipur
6radhakumari10000jaipur
7ramkumar20000usa

mysql having clause example

               select        firstname , count(*)
 
                from   users
                
                group by    firstname
                
                having     salary<500000

output :

firstnamecount(*)
sandeep1
ram2
shyam2
ankit1
radha1

the difference between WHERE and HAVING is the WHERE clause operates on all the records in a table, retrieving only those that match the stated condition. A HAVING clause, on the other hand, operates on records after they have been retrieved from the table, to winnow down the result set even further. Typically, the HAVING clause appears in conjunction with the GROUP BY clause, whereas the WHERE clause appears in SELECT, DELETE, and UPDATE statements.