What is the BETWEEN operator in SQL?

The BETWEEN operator is used in the WHERE clause of SQL to select values within a specified range. The range includes both the start and end values.

Syntax:

SELECT column_list
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

• column_list: Columns to select.
• table_name: The table from which data is retrieved.
• column_name: The column to which the BETWEEN operator is applied.
• value1: The start value of the range.
• value2: The end value of the range.

Example usage: Select all customers whose age is between 18 and 65 inclusive.

SELECT *
FROM Customers
WHERE Age BETWEEN 18 AND 65;

BETWEEN is equivalent to using the >= and <= operators. Example with >= and <=:

SELECT *
FROM Customers
WHERE Age >= 18 AND Age <= 65;

The BETWEEN operator can also be used for text and datetime values.

Example with text values: Select products whose names start with letters from 'A' to 'C'.

SELECT *
FROM Products
WHERE ProductName BETWEEN 'A' AND 'Cez'; -- 'Cez' is used to include all names starting with 'C'

Example with datetime values: Select orders made within a specific time period.

SELECT *
FROM Orders
WHERE OrderDate BETWEEN '2023-01-01' AND '2023-12-31';

To exclude values from the range, use the NOT BETWEEN operator.

Syntax:

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

Example of using NOT BETWEEN: Select all customers whose age is not in the range from 18 to 65 inclusive.

SELECT *
FROM Customers
WHERE Age NOT BETWEEN 18 AND 65;