SQL - BETWEEN Operator

---

---

The SQL BETWEEN Operator

The BETWEEN operator is a logical operator in SQL, that is used to retrieve the data within a specified range. The retrieved values can be integers, characters, or dates.

You can use the BETWEEN operator to replace a combination of "greater than equal AND less than equal" conditions.

Let us understand it in a better way by using the below example table −

Suppose we want to list out the names from the above table who are aged BETWEEN 20 and 30. So, we will get "Varma(21)", "Nikhil(25)", and "Bhargav(29)" as a result.

Syntax

Following is the syntax of the BETWEEN operator in SQL −

SELECT column1, column2, column3,....columnN
FROM table_name
WHERE column BETWEEN value1 AND value2;

Here,

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

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;  

Output

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

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

Output

Following is the output of the above query −

BETWEEN Operator with IN Operator

In SQL, we can combine the BETWEEN operator with the IN operator to select values that are within a specified range and also matches with values specified in the list of IN clause.

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

Output

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

BETWEEN Operator with UPDATE Statement

We can also use the BETWEEN operator with the UPDATE statement to update values within the specified range. The UPDATE statement is used to modify existing data in a database table.

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;

Output

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 −

BETWEEN Operator with DELETE Statement

We can also use the BETWEEN operator with the DELETE statement to delete rows within a specified range.

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;  

Output

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 −

NOT BETWEEN Operator

The NOT BETWEEN operator in SQL works exactly opposite to BETWEEN operator. This is used to retrieve the data which is not present in the specified range.

Let us understand in a better way by using the below example table −

Suppose we want to list out the students from the above table who are aged not between 20 and 30. So, we will get "Prudhvi(45) and Ganesh(33)" as result.

Syntax

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

SELECT column_name1, column_name2, column_name3,......column_nameN
FROM table_name
WHERE column_name NOT BETWEEN value1 AND value2;

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;

Output

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

NOT BETWEEN Operator with IN

We can use the NOT BETWEEN operator in combination with the IN operator to select values that are outside a range and also do not match with the specified list of values.

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

Output

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