MariaDB Between

In this comprehensive MariaDB tutorial, I’ll walk you through everything you need to know about the MariaDB BETWEEN operator—a powerful clause for querying data within specific ranges with multiple examples.

MariaDB Between

What is the BETWEEN Operator in MariaDB?

The BETWEEN operator in MariaDB is a comparison operator that allows you to test if a value falls within a specified range. The BETWEEN clause functions as a flexible filter for search queries and can be used with various data types including dates, numbers, and text.

At its core, the BETWEEN operator tests whether a value is:

• Greater than or equal to a minimum value, AND
• Less than or equal to a maximum value

The BETWEEN operator returns 1 (true) if the expression is within the specified range, and 0 (false) if it’s not. This is equivalent to writing (min <= expr AND expr <= max) if all arguments share the same data type.

In this section, we will learn how to use the MariaDB BETWEEN condition in the query, which is explained with the help of an illustrated example and syntax.

In a SELECT, INSERT, UPDATE, or DELETE query, the BETWEEN condition is used to retrieve values within a specified range. Let’s see the syntax of the MariaDB BETWEEN condition with the following query:

SYNTAX:

SELECT expression FROM TABLE_NAME
WHERE EXPRESSION BETWEEN VALUE_1 and VALUE_2;

The syntax explanation:

• EXPRESSION: A column_name or calculation on the column by using any function, which is done in the WHERE condition.
• VALUE_1 and VALUE_2: These values produce a range from which the expression is created.

NOTE

The MariaDB BETWEEN condition returns records whose expression falls between VALUE_1 and VALUE_2. Use the CAST function to directly convert the data to dates while using the MariaDB BETWEEN clause with dates.

Example 1: Basic Operation

First, let’s have a look at the STATES_OF_USA table by the following query:

SELECT * FROM STATES_OF_USA;

The MariaDB SELECT statement will retrieve all records from the STATES_OF_USA table.

Let’s see an illustrated example of the BETWEEN condition with the following query:

SELECT STATE_NAME,STATE_POPULATION FROM STATES_OF_USA
WHERE STATE_ID BETWEEN 10 AND 20;

In this query, we retrieve records from the STATE_NAME and STATE_POPULATION columns of the STATES_OF_USA table using the WHERE condition. In the WHERE condition, we select records from the STATE_ID column between 10 and 20 using the BETWEEN condition.

If the BETWEEN condition in the WHERE condition is TRUE, the SELECT statement will collect data from the STATES_OF_USA table for both fields STATE_NAME and STATE_POPULATION.

After executing the above query, I got the expected output as shown in the screenshot below.

Read: MariaDB Median

Example 2: BETWEEN with the AND

Here, we will learn how to use and understand the BETWEEN condition in conjunction with the AND condition in a query. It is explained with the help of syntax and an illustrated example.

In MariaDB, the AND condition is also known as the AND operator. And it is used to test two or more conditions in the SELECT, UPDATE, INSERT, and DELETE statements. Let’s have a look at the syntax of MariaDB BETWEEN condition with the AND condition by the following query:

SYNTAX:

SELECT expression FROM TABLE_NAME 
WHERE COLUMN_NAME BETWEEN VALUE_1 and VALUE_2
AND [condition];

Here is the illustrated example of the MariaDB BETWEEN condition with the AND condition by the following query:

EXAMPLE:

SELECT STATE_NAME,STATE_SHORTFORM FROM STATES_OF_USA
WHERE STATE_ID BETWEEN 5 AND 15
AND STATE_ID>=10;

The WHERE condition is used in the previous query to get records from the STATE_NAME and STATE_SHORTFORM columns of the STATES_OF_USA table. The STATE_ID column will select values between 5 and 15 in the WHERE condition. We’ll utilize this condition in the AND operator to see if the value of the STATE_ID column is greater than or equal to 10.

If both the BETWEEN and the AND conditions are TRUE in the WHERE condition, the SELECT statement will obtain data from the STATES_OF_USA table for both columns STATE_NAME and STATE_SHORTFORM.

After executing the above query, I got the expected output as shown in the screenshot below.

Read: MariaDB Not Equal Operator

Example 3: Between Dates

We will understand and learn how to use the BETWEEN condition on the DATE column in the query, which is illustrated with a clear example.

In MariaDB, we have used the date values in the YYYY-MM-DD format for the BETWEEN condition. Here is an illustrated example of the BETWEEN condition on the DATES column by the following query:

EXAMPLE:

SELECT * FROM employee
WHERE `CURRENT_DATE` BETWEEN '2021-03-04' AND '2021-06-30' ; 

In this query, we must use the WHERE condition to retrieve all records from the EMPLOYEE table. However, the WHERE condition is TRUE only if the values of the CURRENT_DATE column give date values between 2021-03-04 and 2020-06-30 from the EMPLOYEE table when using the SELECT statement.

To remember, the CURRENT_DATE column is a reserved word used in the EMPLOYEE table, i.e, we have used the backlash single dash sign as ” for it in the query.

After executing the above query, I got the expected output as shown in the screenshot below.

Read: MariaDB Delete From Statement

Example 4: Between Foreign Key

Here, we will learn how to use the BETWEEN condition on the FOREIGN KEY column in a query, which is explained with the help of an illustrated example.

In MariaDB, a foreign key is a column or a collection of columns in one table that refer to a column or set of columns in another table, ensuring that the two tables’ information systems are secure.

A foreign key contained in the child table is a reference to the primary key included in the parent table. Let’s have a look at the SKULLCANDY table having a foreign key column as the SKULLCANDY_ID column by the following query:

SELECT * FROM SKULLCANDY;

The MariaDB SELECT statement will retrieve all records from the SKULLCANDY table.

Here is the illustrated example of the MariaDB BETWEEN condition on the SKULLCANDY table by the following query:

EXAMPLE:

SELECT NAME FROM SKULLCANDY
WHERE SKULLCANDY_ID BETWEEN 5 AND 10;

The WHERE condition is used to get records from the SKULLCANDY table’s NAME column, as seen in the above query. We’ve chosen records from the SKULLCANDY_ID column that are between 5 and 10 in the WHERE condition. If the WHERE condition is TRUE and the BETWEEN condition is TRUE, the SKULLCANDY table will be queried for records in the NAME column.

After executing the above query, I got the expected output as shown in the screenshot below.

Read: MariaDB Max Connections

Example 5: BETWEEN with JOIN Operations

Let’s understand how to use the MariaDB BETWEEN condition with the JOIN condition for two tables in the query and which is explained with the help of a demonstrated example.

In MariaDB, JOIN is used to merge the rows from more than one table based on common columns in tables. In other words, The data is extracted from more than one table into a single table using the JOIN clause.

• The JOIN clause can be used when there are two or more two tables with common columns.

There are four types of JOIN in MariaDB:

• INNER JOIN: It is a simple JOIN that retrieves all the rows from more than one table where the JOIN condition is True.
• LEFT JOIN: It is a LEFT OUTER JOIN that retrieves all the rows from the left table based on whatever is specified in the ON condition and returns those rows from another table where the JOIN condition is True.
• RIGHT JOIN: It is a RIGHT OUTER JOIN that retrieves all the rows from the right table based on whatever is specified in the ON condition and returns those rows from another table where the JOIN condition is True.
• CROSS JOIN: It returns the result set where each row in a table is joined with each row in another table.

Here is an illustrated example of the BETWEEN condition with the INNER JOIN clause by the following query:

EXAMPLE:

SELECT SKULLCANDY_USA.SKULLCANDY_NAME,SKULLCANDY_USA.SKULLCANDY_USPRICE,SKULLCANDY.NAME
FROM SKULLCANDY_USA
INNER JOIN SKULLCANDY
ON SKULLCANDY_USA.SKULLCANDY_ID=SKULLCANDY.ID
WHERE SKULLCANDY.PRICE BETWEEN 50.99 AND 100.99;

Using the INNER JOIN condition, we retrieve the SKULLCANDY_NAME and SKULLCANDY_USPRICE entries from the SKULLCANDY_USA table, as well as the NAME column from the SKULLCANDY table.

If the SKULLCANDY_ID and ID columns for both the SKULLCANDY_USA and SKULLCANDY tables met, the ON condition was true.

We’re utilizing the BETWEEN condition based on the WHERE condition to fulfill the PRICE column of the SKULLCANDY table’s minimum and maximum values of 50.99 and 100.99, respectively.

If the WHERE condition matches the criteria, TRUE is returned, and the records for the result set are obtained using the SELECT statement from the SKULLCANDY_USA and SKULLCANDY tables.

After executing the above query, I got the expected output as shown in the screenshot below.

Read: What is MariaDB Sharding

Example 6: Between Json Field

Here we will understand how to use the BETWEEN condition on the JSON data type in the query and we will understand it with the help of an illustrated example.

The MariaDB JSON is an alias for LONGTEXT that was created to make it compatible with MySQL’s JSON data type. Because the JSON data format violates the MariaDB standard, MariaDB implements it as a LONGTEXT instead, and MariaDB’s benchmarks show that performance is at least similar. 

Here is the illustrated example of the MariaDB BETWEEN condition on the JSON data type by the following query:

EXAMPLE:

SELECT USER_NAME FROM json_demo 
WHERE USER_NAME BETWEEN 'JohnY_K' AND 'QUEEN_K';

The WHERE condition is used in the previous query to obtain records of the USER_NAME column from the JSON_DEMO table. Using the BETWEEN condition in the WHERE condition, we extract the USER_NAME column between the string names ‘JohnY_K‘ and ‘QUEEN_K‘.

If the WHERE condition is TRUE and the BETWEEN condition is TRUE, the SELECT statement will fetch data from the JSON_DEMO table for the USER_NAME column.

After executing the above query, I got the expected output as shown in the screenshot below.

Read: MariaDB Rename Table

Example 7: Between Order by

In this section, we will understand how to use the BETWEEN function with the ORDER BY clause in the query and which is explained with the help of an example.

In MariaDB, the ORDER BY clause is used to arrange the records in the ascending or descending order in the result set.

Here is the sample example of using the MariaDB BETWEEN condition with the ORDER BY clause by using the following query:

EXAMPLE:

SELECT * FROM STATES_OF_USA
WHERE STATE_ID BETWEEN 10 and 20 
ORDER BY STATE_NAME DESC;

• In the preceding query, we retrieve records from the STATES_OF_USA table using the WHERE condition.
• In the WHERE condition, the STATE_ID column collects records of the value_1 and value_2 from 10 to 20 using the BETWEEN condition.
• And at the end of the query, we have used the ORDER BY clause to arrange the STATE_NAME column in descending order rising the DESC keyword.
• So, if the WHERE condition gets TRUE, it will retrieve results from the STATES_OF_USA table by using the SELECT statement.

Performance Optimization Tips

For optimal performance when using BETWEEN:

• Use Indexed Columns: Ensure the column you’re applying BETWEEN to is indexed, especially for large tables
• Avoid Type Conversions: Keep data types consistent when possible
• Consider Range Alternatives: For very large ranges, sometimes separate > and < operators might perform better

Conclusion

The MariaDB BETWEEN operator is an essential for any database developer. Its simple syntax makes range filtering so simple and readable, while its flexibility works across various data types. This operator is fundamental for testing if values fall within specific ranges.

By knowing BETWEEN, you’ll write more efficient queries. Whether you’re filtering products by price, orders by date, or users by age, BETWEEN provides a straightforward way to implement the logic in your MariaDB databases.

Remember that BETWEEN is inclusive of both its minimum and maximum values, and always be mindful of data type conversions when comparing different types of values.

I hope this comprehensive guide helps you with the usage of the BETWEEN operator in your database applications.

Also, take a look at some more related MariaDB tutorials.

• MariaDB EXPLAIN
• MariaDB ENUM – Helpful Guide
• MariaDB Insert If Not Exists
• MariaDB Greatest Function
• MariaDB Queries – Detailed Guide
• MariaDB JSON Function + Examples
• MariaDB Drop Table + Examples