SQL - BETWEEN Operator

---

---

The SQL BETWEEN Operator

The BETWEEN operator in SQL is used to filter data within a specific range of values. It can be applied to numeric, date, or text columns. The range specified with BETWEEN is inclusive, meaning it includes both the start and end values.

Using BETWEEN can simplify queries by replacing a combination of >= (greater than or equal to) and <= (less than or equal to) conditions.

Syntax

Following is the syntax of the BETWEEN operator in SQL:

SELECT column1, column2
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Here,

• value1 is the beginning value of the range (inclusive).
• value2 is the ending value of the range (inclusive).

SQL BETWEEN with Numeric Values

The BETWEEN operator can be used to filter rows based on a range of numeric values. It is inclusive, meaning the start and end values are included in the results.

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)
); 

Once the table is created, let us insert some values into the 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:

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;  

When we execute the above query, the output is obtained as follows:

SQL BETWEEN with Text Values

The BETWEENoperator can also be used with text (string) values to filter rows based on alphabetical ranges. The comparison follows the database's collation rules, usually in ascending order.

Example

Here, we are using the BETWEEN operator with characters. Let us retrieve the details of the customers whose names starts in between the alphabets "A" and "L" using the following query:

SELECT * FROM CUSTOMERS WHERE NAME BETWEEN 'A' AND 'L';  

Following is the output of the above query:

SQL BETWEEN with Date Values

The BETWEEN operator can also be used to filter rows based on a range of date values. It is inclusive, meaning it includes both the start and end dates in the results.

Example

Suppose we have a table named ORDERS with a column ORDER_DATE. First, we create the table:

CREATE TABLE ORDERS (
   ORDER_ID INT NOT NULL,
   CUSTOMER_NAME VARCHAR(20) NOT NULL,
   ORDER_DATE DATE NOT NULL,
   AMOUNT DECIMAL(10, 2),
   PRIMARY KEY (ORDER_ID)
);

Next, we insert some sample data into the table:

INSERT INTO ORDERS VALUES
(1, 'Ramesh', '2025-01-05', 200.00),
(2, 'Khilan', '2025-03-12', 150.00),
(3, 'Kaushik', '2025-05-20', 300.00),
(4, 'Chaitali', '2025-07-15', 650.00),
(5, 'Hardik', '2025-09-10', 850.00),
(6, 'Komal', '2025-11-25', 450.00);

Now, let us use the BETWEEN operator to retrieve all orders placed between '2025-03-01' and '2025-08-01':

SELECT * FROM ORDERS WHERE ORDER_DATE BETWEEN '2025-03-01' AND '2025-08-01';

The output of the above query will be:

As you can see, only the orders whose ORDER_DATE falls between the specified range are returned.

SQL BETWEEN with IN Operator

The BETWEEN operator can be combined with the IN operator to filter rows that fall within a range for multiple columns or values. This allows checking several columns or values against specified ranges in a single query.

Syntax

Following is the syntax to use the SQL BETWEEN operator with the IN operator:

SELECT column1, column2
FROM table_name
WHERE column1 IN (value1, value2, value3) 
  AND column2 BETWEEN start_value AND end_value;

Example

In the following query, we are retrieving the details of all the customers whose salary is between 4000 and 10000. In addition, we are only retrieving the customers who lives in Hyderabad and Bhopal using the IN operator in SQL:

SELECT * FROM CUSTOMERS
WHERE SALARY BETWEEN 4000 AND 10000
  AND ADDRESS IN ('Hyderabad', 'Bhopal');

On executing the above query, the output is displayed as follows:

SQL BETWEEN with UPDATE Statement

The BETWEEN operator can be used in an UPDATE statement to modify rows that fall within a specific range. It helps to apply conditional updates based on numeric, date, or text ranges.

Syntax

Following is the syntax to use the SQL BETWEEN operator with the UPDATE statement:

UPDATE table_name
SET column_to_update = new_value
WHERE column_to_check BETWEEN start_value AND end_value;

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 25 AND 30;

The output for the above query is as given below:

Query OK, 3 rows affected (0.02 sec)
Rows matched: 3  Changed: 3  Warnings: 0

Verification

Let us verify whether the salaries are updated or not using the following query:

SELECT * FROM CUSTOMERS;  

The table for the above query produced as given below:

SQL BETWEEN with DELETE Statement

The BETWEEN operator can be used in a DELETE statement to remove rows that fall within a specific range. It allows conditional deletion based on numeric, date, or text ranges.

Syntax

Following is the syntax to use the SQL BETWEEN operator with the DELETE statement:

DELETE FROM table_name
WHERE column_name BETWEEN start_value AND end_value;

Example

Now, let us delete the customers whose age is between 20 and 24 using the DELETE statement:

DELETE FROM CUSTOMERS 
WHERE AGE BETWEEN 20 AND 24;  

If we compile and run the above query, the result is produced as follows:

Query OK, 3 rows affected (0.02 sec)

Verification

Let us verify whether the records with the specified age values are deleted or not, using the following query:

SELECT * FROM CUSTOMERS;

The table for the above query produced is as given below:

SQL NOT BETWEEN Operator

The NOT BETWEEN operator in SQL is used to filter rows that fall outside a specified range. It works with numeric, date, or text values, returning all records that are not within the given start and end values.

Syntax

Following is the syntax of the NOT BETWEEN operator in SQL:

SELECT column1, column2, ...
FROM table_name
WHERE column_name NOT BETWEEN start_value AND end_value;

Example

Consider the previously created CUSTOMERS table and let us retrieve the details of customers whose age is not greater than or equal to 25 and less than or equal to 30 (numeric data) using the following query:

SELECT * FROM CUSTOMERS 
WHERE AGE NOT BETWEEN 25 AND 30;

When we execute the above query, the output is obtained as follows:

SQL NOT BETWEEN with Text Values

The NOT BETWEEN operator can be used with text (string) values to exclude rows where a column's value falls within a specific alphabetical range. It is the opposite of BETWEEN and is inclusive of the boundary values. Text comparison is based on the collation and character order of the database.

Example

Suppose we want to retrieve customers whose names do not fall alphabetically between 'A' and 'L':

SELECT * FROM CUSTOMERS
WHERE NAME NOT BETWEEN 'A' AND 'L';

We get the output as shown below:

SQL NOT BETWEEN with IN Operator

You can combine the NOT BETWEEN operator with the IN operator to filter rows where a column does not fall within a specific range for multiple specified values. This allows more precise conditional filtering across a set of columns or values.

Syntax

Following is the syntax of the SQL NOT BETWEEN operator with the IN operator:

SELECT column1, column2, ...
FROM table_name
WHERE column_name IN (value1, value2, ...)
  AND column_name NOT BETWEEN start_value AND end_value;

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 customers who are living in Bhopal using the IN operator in SQL:

SELECT * FROM CUSTOMERS 
WHERE SALARY NOT BETWEEN 1000 AND 5000 
  AND ADDRESS NOT IN ('Bhopal');

On executing the above query, the output is displayed as follows: