SQL BETWEEN Operator

The BETWEEN operator in SQL is used to select data within a specific range of values. It retrieves data where a specified column value is within a range of values specified in the BETWEEN clause.

Syntax of SQL BETWEEN operator

The syntax for using the BETWEEN operator is as follows:

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

In the above syntax,

• column_name(s) is the name of the column(s) that you want to retrieve data from.
• table_name is the name of the table that you want to retrieve data from.
• column_name is the name of the column that you want to compare with the range of values specified in the BETWEEN clause.
• value1 and value2 are the values that define the range of values for the BETWEEN operator.

Consider the following “employees” table:

ID	Name	Age	City
1	John Doe	32	New York
2	Jane Doe	28	London
3	Jim Smith	35	Paris
4	Amy Johnson	26	Berlin
5	Tom Brown	40	Rome

The following SQL statement selects all employees whose age is between 28 and 35:

SELECT *
FROM employees
WHERE age BETWEEN 28 AND 35;

The result of the above SQL statement will be:

ID	Name	Age	City
1	John Doe	32	New York
2	Jane Doe	28	London
3	Jim Smith	35	Paris

Example 1: SQL BETWEEN operator with Dates

Consider the following “orders” table:

OrderID	OrderDate	CustomerID	Amount
1	2022-01-01	C001	100
2	2022-02-01	C002	200
3	2022-03-01	C003	300
4	2022-04-01	C004	400
5	2022-05-01	C005	500

The following SQL statement selects all orders that were placed between ‘2022-02-01’ and ‘2022-04-01’:

SELECT *
FROM orders
WHERE OrderDate BETWEEN '2022-02-01' AND '2022-04-01';

The result of the above SQL statement will be:

OrderID	OrderDate	CustomerID	Amount
2	2022-02-01	C002	200
3	2022-03-01	C003	300
4	2022-04-01	C004	400

Example 2: SQL BETWEEN operator with Text Values

Consider the following “products” table:

ProductID	ProductName	Category	Price
P001	Apple	Fruit	2
P002	Banana	Fruit	1.5
P003	Carrot	Vegetable	1
P004	Grapes	Fruit	3
P005	Lemon	Fruit	2.5

The following SQL statement selects all products that have a product name between “Apple” and “Lemon”:

SELECT *
FROM products
WHERE ProductName BETWEEN 'Apple' AND 'Lemon';

The result of the above SQL statement will be:

ProductID	ProductName	Category	Price
P001	Apple	Fruit	2
P005	Lemon	Fruit	2.5

Example 3: SQL BETWEEN operator with Numeric Values

Consider the following “employees” table:

EmployeeID	EmployeeName	Salary	Department
E001	John	5000	Marketing
E002	Jane	5500	Sales
E003	Bob	4500	IT
E004	Alice	4000	HR
E005	Eve	4700	Finance

The following SQL statement selects all employees whose salary is between 4000 and 5000:

SELECT *
FROM employees
WHERE Salary BETWEEN 4000 AND 5000;

The result of the above SQL statement will be:

EmployeeID	EmployeeName	Salary	Department
E004	Alice	4000	HR
E003	Bob	4500	IT

The BETWEEN operator can be used with numeric values, date and time values, and even string values, depending on the data type of the columns involved in the comparison. It provides a simple way to select a range of values within a given column.

Important points to consider while using SQL BETWEEN operator

• The BETWEEN operator is inclusive, meaning both the specified values are included in the results.
• The BETWEEN operator can be used with different data types like numbers, dates, and texts, but make sure the column and the specified values have the same data type.
• In some databases, the BETWEEN operator can also be used in an INNER JOIN clause.
• To exclude a specific value, use the NOT BETWEEN operator instead of BETWEEN operator.

Conclusion

The SQL BETWEEN operator is a useful tool for selecting data within a specific range of values. It is important to understand the syntax and use it properly to get the desired results. Understanding the different data types and their compatibility with the BETWEEN operator can also be helpful in achieving accurate results.