Create Tables Show Tables Alter Tables Rename Tables Clone Tables Truncate Tables Temporary Tables Repair Tables Describe Tables Add/Delete Columns Show Columns Rename Columns Table Locking Drop Tables Derived Tables

Queries Constraints Insert Query Select Query Update Query Delete Query Replace Query Insert Ignore Insert on Duplicate Key Update Insert Into Select

Create Views Update Views Drop Views Rename Views

Indexes Create Index Drop Index Show Indexes Unique Index Clustered Index Non-Clustered Index

Using Joins Inner Join Left Join Right Join Cross Join Full Join Self Join Delete Join Update Join Union vs Join

Unique Key Primary Key Foreign Key Composite Key Alternate Key

Triggers Create Trigger Show Trigger Drop Trigger Before Insert Trigger After Insert Trigger Before Update Trigger After Update Trigger Before Delete Trigger After Delete Trigger

Data Types VARCHAR BOOLEAN ENUM DECIMAL INT FLOAT BIT TINYINT BLOB SET

Regular Expressions RLIKE Operator NOT LIKE Operator NOT REGEXP Operator regexp_instr() Function regexp_like() Function regexp_replace() Function regexp_substr() Function

Fulltext Search Natural Language Fulltext Search Boolean Fulltext Search Query Expansion Fulltext Search ngram Fulltext Parser

Date and Time Functions Arithmetic Operators Numeric Functions String Functions Aggregate Functions

Useful Functions Statements Reference Quick Guide Useful Resources Discussion

Feedback

Submit request if you have any questions.

Course

Between Operator

MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

Between Operator
MySQL Between Operator
The BETWEEN operator in MySQL is a logical operator provided by SQL, that is used to restrict the range from which the data values are to be retrieved. The retrieved values can be integers, characters, or dates.
You can use BETWEEN operator to replace a combination of “greater than equal AND less than equal” conditions.
Let us understand in a better way by using the following example table 
﻿
Syntax
Following is the syntax of the BETWEEN operator in MySQL 
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
Here,
value1 is the beginning value of the range.
value2 is the ending value of the range (inclusive).
Example
First of all, let us create a table named CUSTOMERS using the following query 
CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
); 
Let us insert some values into this table using the following INSERT query 
INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );
The table created is as follows 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
6
Komal
22
Hyderabad
4500.00
7
Muffy
24
Indore
10000.00
Now, we are using the BETWEEN operator to retrieve the details of the CUSTOMERS whose AGE (numeric data) is between 20 and 25
SELECT * FROM CUSTOMERS 
WHERE AGE BETWEEN 20 AND 25;  
Output
When we execute the above query, the output is obtained as follows 
ID
NAME
AGE
ADDRESS
SALARY
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
4
Chaitali
25
Mumbai
6500.00
6
Komal
22
Hyderabad
4500.00
7
Muffy
24
Indore
10000.00
MySQL BETWEEN with IN Operator
The BETWEEN operator and the IN operator can be used together in a MySQL query, to select values that are within a specified range and also match with specified values.
Syntax
Following is the syntax of the BETWEEN operator in MySQL 
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2
AND column_name IN (list_of_values);
Example
In this example, we are selecting all the customers whose salary is between 4000 and 10000. In addition; we are only retrieving the customers who are living in MP and Bhopal using IN operator in SQL.
SELECT * FROM CUSTOMERS 
WHERE SALARY BETWEEN 4000 AND 10000 
AND ADDRESS IN ('Hyderabad', 'Bhopal');
Output
The following is obtained 
ID
NAME
AGE
ADDRESS
SALARY
5
Hardik
27
Bhopal
8500.00
6
Komal
22
Hyderabad
4500.00
MySQL BETWEEN with UPDATE statement
The UPDATE statement in MySQL is used to modify existing data in a database table. Using the BETWEEN operator in an UPDATE statement to update values within the specified range.
Example
Let us update the salaries of the customers whose age lies between 25 to 30 using the following query 
UPDATE CUSTOMERS 
SET SALARY = 10000 
WHERE AGE BETWEEN 20 AND 25;
Verification
Let us verify whether the salaries are updated or not using the following query 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
10000.00
3
Kaushik
23
Kota
10000.00
4
Chaitali
25
Mumbai
10000.00
5
Hardik
27
Bhopal
8500.00
6
Komal
22
Hyderabad
10000.00
7
Muffy
24
Indore
10000.00
BETWEEN operator with DELETE statement
We can also use the BETWEEN operator in a DELETE statement to delete rows within a specified range.
Example
Now, let us delete the customers whose age is between 18 and 20 using the DELETE command.
DELETE FROM CUSTOMERS 
WHERE AGE BETWEEN 20 AND 22;  
Verification
Let us verify whether the specified aged employees are deleted or not using the following query 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
2
Khilan
25
Delhi
1500.00
3
Kaushik
23
Kota
2000.00
4
Chaitali
25
Mumbai
6500.00
5
Hardik
27
Bhopal
8500.00
7
Muffy
24
Indore
10000.00
MySQL NOT BETWEEN Operator
The NOT BETWEEN operator in MySQL is a negation of the BETWEEN operator. This is used to retrieve the data which is not present in the specified range or time interval.
Syntax
Following is the syntax of the NOT BETWEEN operator in MySQL 
SELECT column_name(s)
FROM table_name
WHERE column_name NOT BETWEEN value1 AND value2;
Example
Consider the CUSTOMERS table to retrieve the details of customers whose age is not between 20 and 25 (numeric data) using the following query.
SELECT * FROM CUSTOMERS 
WHERE AGE NOT BETWEEN 20 AND 25;
Output
Following is the output 
ID
NAME
AGE
ADDRESS
SALARY
1
Ramesh
32
Ahmedabad
2000.00
5
Hardik
27
Bhopal
8500.00
NOT BETWEEN operator with IN operator
Like the BETWEEN operator, we can also use the NOT BETWEEN operator in combination with the IN operator. This is to select values that fall outside a range and also do not match with the specified values.
Example
In the following query, we are selecting the customers whose salary is NOT between 1000 and 5000. In addition; we are not retrieving the employees who are living in Bhopal using IN operator in SQL.
SELECT * FROM CUSTOMERS 
WHERE SALARY NOT BETWEEN 1000 AND 5000 
AND ADDRESS NOT IN ('Bhopal');
Output
Following is the output 
ID
NAME
AGE
ADDRESS
SALARY
4
Chaitali
25
Mumbai
6500.00
7
Muffy
24
Indore
10000.00
Between Operator Using Client Program
We can also apply the BETWEEN operator on a MySQL table using a client program.
Syntax
Following are the syntaxes of the Between Operator in MySQL table in various programming languages 
PHPNodeJSJavaPython
To execute the Between Operator in MySQL through a PHP program, we need to execute the SQL query with BETWEEN statement using the mysqli function named query() as 
$sql = "SELECT column1, column2, ... FROM table_name 
WHERE column_name BETWEEN value1 AND value2";
$mysqli->query($sql);
Example
Following are the implementations of this operation in various programming languages 
PHPNodeJSJavaPython
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if($mysqli->connect_errno ) {
   printf("Connect failed: %s", $mysqli->connect_error);
   exit();
}
//printf('Connected successfully.');
$sql = "SELECT * FROM CUSTOMERS WHERE SALARY BETWEEN 4000 AND 10000";
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   printf("Table records: \n");
   while($row = $result->fetch_assoc()) {
      printf("Id %d, Name: %s, Age: %d, Address %s, Salary %f",   
      $row["ID"],
      $row["NAME"],
      $row["AGE"],
      $row["ADDRESS"],
      $row["SALARY"]);
      printf("\n");         
   }
} else {
   printf('No record found.');
}
mysqli_free_result($result);
$mysqli->close()
Output
The output obtained is as follows 
Table records:
Id 5, Name: Hardik, Age: 27, Address Vishakapatnam, Salary 8500.000000
Id 6, Name: Komal, Age: 0, Address Vishakapatnam, Salary 4500.000000
Id 7, Name: Muffy, Age: 24, Address , Salary 10000.000000    
﻿
﻿
﻿
﻿
﻿
﻿

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

Between Operator

MySQL Between Operator

Syntax

Example

Output

MySQL BETWEEN with IN Operator

Syntax

Example

Output

MySQL BETWEEN with UPDATE statement

Example

Verification

BETWEEN operator with DELETE statement

Example

Verification

MySQL NOT BETWEEN Operator

Syntax

Example

Output

NOT BETWEEN operator with IN operator

Example

Output

Between Operator Using Client Program

Syntax

Example

Output