MariaDB Between

Summary: in this tutorial, you will learn how to use the MariaDB between operator to test if a value is between two other values.

Introduction to MariaDB between operator

The MariaDB between operator is a logical operator that returns true if a value is between two values

The between operator is typically used in the where clause to form a search condition.

Here is the syntax of the between operator:

expression between low and high
Code language: SQL (Structured Query Language) (sql)

In this syntax, low and high are literal values or expressions. The low value must be less than the high value.

The between operator returns true if the expression is less than or equal to the high value and greater than or equal to the low value.

In other words, the between operator is syntactic sugar of the following expression:

expression >= low and expression <= high
Code language: SQL (Structured Query Language) (sql)

To negate the between operator, you use the not operator.

expression not between low and high
Code language: SQL (Structured Query Language) (sql)

The not between returns true if the expression is not between two other values, or:

expression < low or expression > high
Code language: SQL (Structured Query Language) (sql)

MariaDB between operator examples

We’ll use the countries table form the sample database for the demonstration:

A) Using the MariaDB between operator with numbers example

The following example uses the between operator to find countries that have areas between 1,566,500 and 2,780,400:

select 
    name, 
    area 
from 
    countries
where 
    area between 1566500 
        and 2780400
order by 
    area;
Code language: SQL (Structured Query Language) (sql)

Note that the above query is equivalent to the following query:

 select 
    name, 
    area 
from 
    countries
where 
    area >= 1566500 and
    area <= 2780400
order by 
    area;
Code language: SQL (Structured Query Language) (sql)

B) Using MariaDB between operator with dates example

The following statement uses the between operator to find countries which have national days that fall between August 17, 1945 and November 09, 1953:

select 
    name, 
    national_day 
from 
    countries
where 
    national_day 
        between '1945-08-17' 
            and '1953-11-09'
order by 
    national_day;
Code language: SQL (Structured Query Language) (sql)

C) Using MariaDB not between operator example

The following statement uses the not between operator to find countries whose national does not fall between August 17, 1945 and November 09, 1953:

select 
    name, 
    national_day 
from 
    countries
where 
    national_day 
        not between '1945-08-17' 
            and  '1953-11-09'
order by 
    national_day;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the MariaDB between operator to test if a value is between two other values.