MariaDB BETWEEN and NOT BETWEEN Operators


Objectives

  • Understanding of MariaDB BETWEEN Operator

  • MariaDB BETWEEN Operator in Where Condition

  • MariaDB NOT BETWEEN Operator in Where Condition


Create database and table and insert data if it is not created already

--- Create database if not exist
CREATE DATABASE IF NOT EXISTS mariadb_tutorial;

--- Select the database for further operation
USE mariadb_tutorial;

--- Create table if not exist
CREATE TABLE IF NOT EXISTS person (
    id int(12) NOT NULL AUTO_INCREMENT,
    first_name varchar(150) NOT NULL,
    last_name varchar(150),
    email varchar(100),
    age int,
    income double,
    PRIMARY KEY (id)
)

--- Remove old Data
TRUNCATE person;

--- Insert data into table if not exist
INSERT INTO person (first_name, last_name, email, age, income)
VALUES
	('Faiyaz', 'Mia', 'faiyaz@pf.local', 1, 5000),
	('John', 'Doe', 'john@pf.local', 19, 100),
	('Tahsin', NULL, 'tahsin@pf.local', 10, 150),
	('Jane', 'Doe', 'jane@gmail.com', 26, 300),
	('Rakib', 'Mia', 'rakib@bf.local', 24, 200),
	('Sagor', 'Sowrov', 'email10@bf.local', 20, 250),
	('Touhid', NULL, 'hmtmcse.com@gmail.com', 30, 500);


Understanding of MariaDB BETWEEN Operator

BETWEEN operator can find value within a range. For instance, you need person whose income within 100 to 300, this type of case you can use BETWEEN Operator. NOT BETWEEN reverse the result of BETWEEN. The values can be numbers, text, or dates.


MariaDB BETWEEN Operator in Where Condition

Problem: Find person whose incomes within 100 to 300

Syntax

SELECT * FROM table_name WHERE column_name BETWEEN value1 AND value2;

Example

SELECT * FROM person WHERE income BETWEEN 100 AND 300;

Output

+----+------------+-----------+------------------+------+--------+
| id | first_name | last_name | email            | age  | income |
+----+------------+-----------+------------------+------+--------+
|  2 | John       | Doe       | john@pf.local    |   19 |    100 |
|  3 | Tahsin     | NULL      | tahsin@pf.local  |   10 |    150 |
|  4 | Jane       | Doe       | jane@gmail.com   |   26 |    300 |
|  5 | Rakib      | Mia       | rakib@bf.local   |   24 |    200 |
|  6 | Sagor      | Sowrov    | email10@bf.local |   20 |    250 |
+----+------------+-----------+------------------+------+--------+
5 rows in set (0.360 sec)


MariaDB NOT BETWEEN Operator in Where Condition

Problem: Find person whose incomes not range of 100 to 300

Syntax

SELECT * FROM table_name WHERE column_name NOT BETWEEN value1 AND value2;

Example

SELECT * FROM person WHERE income NOT BETWEEN 100 AND 300;

Output

+----+------------+-----------+-----------------------+------+--------+
| id | first_name | last_name | email                 | age  | income |
+----+------------+-----------+-----------------------+------+--------+
|  1 | Faiyaz     | Mia       | faiyaz@pf.local       |    1 |   5000 |
|  7 | Touhid     | NULL      | hmtmcse.com@gmail.com |   30 |    500 |
+----+------------+-----------+-----------------------+------+--------+
2 rows in set (0.001 sec)


MariaDB BETWEEN and NOT BETWEEN Operators Video tutorial

Share