totn MariaDB

MariaDB: BETWEEN Condition

This MariaDB tutorial explains how to use the MariaDB BETWEEN condition with syntax and examples.

Description

The MariaDB BETWEEN condition is used to retrieve values within a range in a SELECT, INSERT, UPDATE, or DELETE statement.

Syntax

The syntax for the BETWEEN condition in MariaDB is:

expression BETWEEN value1 AND value2;

Parameters or Arguments

expression
A column or calculation.
value1 and value2
These values create an inclusive range that expression is compared to.

Note

  • The MariaDB BETWEEN condition will return the records where expression is within the range of value1 and value2 (inclusive).
  • When using the MariaDB BETWEEN condition with dates, be sure to use the CAST function to explicitly convert the values to dates.

Example - With Numeric

Let's look at some MariaDB BETWEEN condition examples using numeric values. The following numeric example uses the BETWEEN condition to retrieve values within a numeric range.

For example:

SELECT *
FROM sites
WHERE site_id BETWEEN 250 AND 325;

This MariaDB BETWEEN example would return all rows from the sites table where the site_id is between 250 and 325 (inclusive). It is equivalent to the following SELECT statement:

SELECT *
FROM sites
WHERE site_id >= 250
AND site_id <= 325;

Example - With Date

Next, let's look at how you would use the MariaDB BETWEEN condition with Dates. When using the BETWEEN condition in MariaDB with dates, be sure to use the CAST function to explicitly convert the values to dates.

The following date example uses the BETWEEN condition to retrieve values within a date range.

For example:

SELECT *
FROM pages
WHERE update_date BETWEEN CAST('2014-05-01' AS DATE) AND CAST('2014-05-31' AS DATE);

This MariaDB BETWEEN condition example would return all records from the pages table where the update_date is between May 1, 2014 and May 31, 2014 (inclusive). It would be equivalent to the following SELECT statement:

SELECT *
FROM pages
WHERE update_date >= CAST('2014-05-01' AS DATE)
AND update_date <= CAST('2014-05-28' AS DATE);

Example - Using NOT Operator

The MariaDB BETWEEN condition can also be combined with the NOT operator. Here is an example of how you would combine the BETWEEN condition with the NOT Operator.

For example:

SELECT *
FROM sites
WHERE site_id NOT BETWEEN 10 AND 20;

This MariaDB BETWEEN example would return all rows from the sites table where the site_id was NOT between 10 and 20, inclusive. It would be equivalent to the following SELECT statement:

SELECT *
FROM sites
WHERE site_id < 10
OR site_id > 20;
Share on: