MariaDB Between + Examples

In this MariaDB tutorial, we will discuss how to use the MariaDB Between condition and look at several examples related to it. There are lists of the topic that comes under discussion:

• MariaDB Between
• MariaDB Between And
• MariaDB Between Dates
• MariaDB Between Foreign Key
• MariaDB Between Join
• MariaDB Between Json Field
• MariaDB Between Order by

MariaDB Between

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

In a SELECT, INSERT, UPDATE or DELETE query, the BETWEEN condition is used to get values inside a limit. Let’s see the syntax of the MariaDB BETWEEN condition by 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 where 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.

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 by the following query:

AN EXAMPLE:

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

In this query, we retrieve records of the STATE_NAME and the STATE_POPULATION column from 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.

Read: MariaDB Median

MariaDB Between And

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

In MariaDB, the AND condition is also known as AND operator. And it is used to test two or more two 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.

Read: MariaDB Not Equal Operator

MariaDB Between Dates

We will understand and learn how to use the BETWEEN condition on the DATE column in the query and which is explained with the help of an illustrated 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.

Just 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.

Read: MariaDB Delete From Statement

MariaDB Between Foreign Key

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

In MariaDB, a foreign key is a column or collection of columns in one table that refers to a column or set of columns in another table, ensuring that the two tables’ informational system is secure.

A foreign key contained in the child table is a reference to the primary key contained 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.

Read: MariaDB Max Connections

MariaDB Between Join

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.

Read: What is MariaDB Sharding

MariaDB 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.

Read: MariaDB Rename Table

MariaDB 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.

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

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

We addressed the MariaDB BETWEEN condition in this MariaDB tutorial, as well as several sample instances connected to it. There are lists of the topic that comes under discussion:

• MariaDB Between
• MariaDB Between And
• MariaDB Between Dates
• MariaDB Between Foreign Key
• MariaDB Between Join
• MariaDB Between Json Field
• MariaDB Between Order by