BETWEEN Clause in MariaDB

MariaDB uses SQL (Structured Query Language) and is an open-source relational database management system (RDBMS) for managing and manipulating data. With the help of the MariaDB DISTINCT clause, you can efficiently extract unique values from a given column or collection of columns in query results. In SELECT queries, this clause is frequently used to remove duplicate rows and show only unique data.

BETWEEN Operator

The BETWEEN clause in a MariaDB is a flexible operator that makes it easier to query data inside a specific range. It is a logical operator and is mainly used with the WHERE Clause.

Syntax:

SELECT field1, field2, ... FROM table_name WHERE column_name BETWEEN value1 AND value2;

The BETWEEN clause allows users to obtain rows if the value of a particular column is within a given range. The result set includes values equal to 1 and 2 as the range is inclusive.

Create Table:

Query:

CREATE TABLE worker (
    worker_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2),
Hire_date DATE
);

Insert Data:

Query:

INSERT INTO worker (first_name, last_name, department, salary, hire_date)
VALUES
    ('Minal', 'Pandey', 'IT', 60000.00, '2022-01-15'),
    ('Kavya', 'Sharma', 'HR', 55000.00, '2022-02-20'),
    ('Kavya', 'Sharma', 'Finance', 70000.00, '2022-03-10'),
    ('Asad', 'Mohammad', 'Marketing', 62000.00, '2022-04-05'),
    ('Vivek', 'Sharma', 'IT', 65000.00, '2022-05-12');

Example 1: Retrieve employees with salaries ranging between 50,000 and 70,000.

Query:

SELECT * FROM worker WHERE salary BETWEEN 50000 AND 65000;

Output:

BETWEEN Operator

Example 2: Get the list of workers who were hired between 2022-01-15 to 2022-03-10.

Query:

SELECT * FROM worker WHERE hire_date BETWEEN '2022-01-15' AND '2022-03-10';

Output:

BETWEEN Operator

Example 3: Get the list of workers whose id lies between 1 to 3.

Query:

SELECT * FROM worker WHERE worker_id BETWEEN 1 AND 3;

Output:

BETWEEN Operator

Example 4: Retrieve the list of workers who were hired between 2022-01-15 to 2022-05-12 and belongs to IT department.

Query:

SELECT * FROM worker WHERE hire_date BETWEEN '2022-01-15' AND '2022-05-12' AND department='IT';

Output:

BETWEEN Operator

Conclusion

Finally, the BETWEEN clause in MariaDB is useful for searching data within specified ranges. The between clause behaves as a flexible filter for search queries that can be used with dates, numeric and other forms of data. This article has shown us how to test if a value is between two other values with the MariaDB operator.

Article Tags :

Databases

MariaDB