BETWEEN Operator in SQL

DevSniper
Published : 04 August 2024

113 Views

In SQL, BETWEEN is a logical operator. BETWEEN operator is used in SQL to filter the result set within a certain range. BETWEEN operator allows you to specify a range of values in a WHERE clause to filter result set. Understanding the BETWEEN operator enhances the ability to perform complex query, retrieve targeted data, and extract valuable insights from datasets. This article provides a detailed overview of the BETWEEN operator with example. After reading this article you can use the BETWEEN operator easily and retrieve valuable data from a table.

The range is inclusive, which means that the boundary values are part of the result. BETWEEN operator can be applied to numbers, text and dates.

Syntax

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

Demo Database

CREATE TABLE `Users` (
   `Id` int NOT NULL AUTO_INCREMENT,
   `Name` varchar(50) NOT NULL,
   `Mobile` varchar(10) DEFAULT NULL,
   `DOB` date DEFAULT NULL,
   `Weight` decimal(10,2) DEFAULT NULL,
   PRIMARY KEY (`Id`)
 );

Id	Name	        Mobile	         DOB	       Weight
-----------------------------------------------------------------
1	Deepak	        9999999991	1993-01-15	60.00
2	Smith	        9999999999	1994-11-20	85.00
3	Naveen	        9999999992	1997-01-15	55.00
4	Deepak Sharma	9999999993	1998-08-03	null
5	Jane Smith	9999999995	1993-10-05	73.00
6	Jone Head	9999999991	1983-01-15	90.00
7	Deepak Kumar	9999999999	1984-11-20	85.00
8	Jack	        9999999992	1987-01-15	59.00
9	Ramesh	        9999999993	1988-08-03	86.00
10	David	        9999999995	1983-10-05	70.00

BETWEEN Operator With Number

BETWEEN Operator is typically useful when you want to filter the result set within a certain range. The range is inclusive that means begin and end values are included.

SQL query to retrieve Users whose Weight is between 60 and 85 (60 and 85 included in result set).

SELECT Id, Name, Mobile, DOB, Weight 
FROM Users 
WHERE Weight BETWEEN 60 and 85;

Id	Name	        Mobile	        DOB	        Weight
----------------------------------------------------------------
1	Deepak	        9999999991	1993-01-15	60.00
2	Smith	        9999999999	1994-11-20	85.00
5	Jane Smith	9999999995	1993-10-05	73.00
7	Deepak Kumar	9999999999	1984-11-20	85.00
10	David	        9999999995	1983-10-05	70.00

BETWEEN Operator With Date

SQL query to retrieve users whose Date of Birth is between 1980-01-01 and 1988-08-03.

SELECT Id, Name, Mobile, DOB, Weight 
FROM Users 
WHERE DOB BETWEEN "1980-01-01" and "1988-08-03";

Id	Name	        Mobile	        DOB	        Weight
----------------------------------------------------------------
6	Jone Head	9999999991	1983-01-15	90.00
7	Deepak Kumar	9999999999	1984-11-20	85.00
8	Jack	        9999999992	1987-01-15	59.00
9	Ramesh	        9999999993	1988-08-03	86.00
10	David	        9999999995	1983-10-05	70.00

BETWEEN Operator With Text

SQL query to retrieve users whose names are between 'deepak' and 'jack'.

SELECT Id, Name, Mobile, DOB, Weight 
FROM Users 
WHERE Name BETWEEN "deepak" and "jack";

Id	Name        	Mobile	        DOB        	Weight
1	Deepak	        9999999991	1993-01-15	60.00
4	Deepak Sharma	9999999993	1998-08-03	null
7	Deepak Kumar	9999999999	1984-11-20	85.00
8	Jack	        9999999992	1987-01-15	59.00

Indexing With BETWEEN

Indexes are fundamental to database optimization, enabling faster retrieval of records. Properly maintained and well-designed indexes are essential for achieving optimal database performance. When used with the BETWEEN operator, indexes can significantly enhance query performance by narrowing down the search range.

How Indexes Work with BETWEEN

When you create an index on a column that used in a BETWEEN query, the database engine can efficiently traverse the index to locate the start and end points of the range. This is much faster than performing a full table scan, especially for large tables.

Key Characteristics of BETWEEN Operator

The BETWEEN operator is a powerful and user-friendly tool for filtering result sets within a specified range.
The BETWEEN works with numeric, date and text data types.
The BETWEEN operator returns True if the value of specified column is greater than or equal to value1 and less than or equal to value2.
It can be used with SELECT, UPDATE, DELETE and INSERT SQL statements.
Using BETWEEN enhances the readability of queries compared to using >= and <=.
Indexes enable the database engine to quickly locate the range of values specified in the BETWEEN clause, speeding up query execution.