Using the SQL BETWEEN Operator: A Comprehensive Guide

The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.

The BETWEEN operator is inclusive: begin and end values are included. 

Syntax:

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Prerequisite Codes

If you don't have table please create below table and practice it.

My database name is SQLExamples

USE SQLExamples

Create this below Employee table

DROP TABLE Employee
CREATE TABLE Employee(
EmpId INT,
EmpName VARCHAR(25),
EmpAddress VARCHAR(100),
EmpDept VARCHAR(25)
)

Insert data inside table

INSERT INTO Employee VALUES
(1, 'Rambo', 'Kolkata', 'IT'),
(2, 'Rohit', 'Kolkata', 'IT'),
(3, 'Rohon', 'Kolkata', 'ITIS'),
(4, 'Ronok', 'Kolkata', 'ITIS'),
(5, 'Rubin', 'Kolkata', 'ITIS'),
(6, 'Sorif', 'Kolkata', 'ADMIN'),
(7, 'Soriful', 'Kolkata', 'ADMIN'),
(8, 'Sofik', 'Kolkata', 'ADMIN')

SELECT * FROM Employee

Output:

The above code will produce the following result-

EmpId	EmpName	EmpAddress	EmpDept
1	Rambo	Kolkata	IT
2	Rohit	Kolkata	IT
3	Rohon	Kolkata	ITIS
4	Ronok	Kolkata	ITIS
5	Rubin	Kolkata	ITIS
6	Sorif	Kolkata	ADMIN
7	Soriful	Kolkata	ADMIN
8	Sofik	Kolkata	ADMIN

Example: BETWEEN

Code:

SELECT * FROM Employee
WHERE EmpId BETWEEN 2 AND 4

Output:

The above code will produce the following result-

EmpId	EmpName	EmpAddress	EmpDept
2	Rohit	Kolkata	IT
3	Rohon	Kolkata	ITIS
4	Ronok	Kolkata	ITIS

Example: NOT BETWEEN

Code:

SELECT * FROM Employee
WHERE EmpId NOT BETWEEN 2 AND 4

Output:

The above code will produce the following result-

EmpId	EmpName	EmpAddress	EmpDept
1	Rambo	Kolkata	IT
5	Rubin	Kolkata	ITIS
6	Sorif	Kolkata	ADMIN
7	Soriful	Kolkata	ADMIN
8	Sofik	Kolkata	ADMIN

Example: BETWEEN with IN

Code:

SELECT * FROM Employee
WHERE EmpId BETWEEN 2 AND 7 AND EmpDept NOT IN ('IT', 'ITIS')

Output:

The above code will produce the following result-

EmpId	EmpName	EmpAddress	EmpDept
6	Sorif	Kolkata	ADMIN
7	Soriful	Kolkata	ADMIN