MariaDB Having

Summary: in this tutorial, you will learn how to use the MariaDB having clause to specify a search condition for groups.

Introduction to MariaDB having clause

The where clause allows you to specify a search condition for filtering rows returned by the select statement. However, it cannot be used for filtering summarized rows created by the group by clause.

To specify a search condition for filtering summarized rows generated by the group by clause, you use the having clause.

The following illustrates the syntax of the having clause:

select
    select_list
from
    table_name
group by
    column1,
    column2,
    ...
having
    search_condition; 
Code language: SQL (Structured Query Language) (sql)

The having clause appears immediately after the group by clause. To form the having clause, you specify a search_condition that may consist of one or more Boolean expressions. The select statement will return only groups  (or rows) which cause the search_condition to evaluate to true.

MariaDB having clause examples

We’ll use the countries and regions tables from the nation sample database:

A) Using the MariaDB having clause to filter group example

The following example uses the having clause to find regions that have more than 10 countries:

select 
    regions.name region,
    count(country_id) country_count
from 
    countries
inner join regions 
    using (region_id)
group by 
    (regions.name)
having 
    count(region_id) > 10
order by 
    country_count desc;
Code language: SQL (Structured Query Language) (sql)

B) Using the MariaDB having clause with a complex condition example

The following example uses the having clause to find countries that have more than 10 countries and total areas greater than 1,000,000 km2:

select 
    regions.name region,
    count(country_id) country_count,
    sum(area) area
from 
    countries
inner join regions 
    using (region_id)
group by 
    (regions.name)
having 
    count(region_id) > 10 and 
    area > 1000000
order by 
    area desc,
    country_count desc;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the MariaDB having clause to specify a search condition for groups.