
- SQL - Home
- SQL - Roadmap
- SQL - Overview
- SQL - RDBMS Concepts
- SQL - Databases
- SQL - Syntax
- SQL - Data Types
- SQL - Operators
- SQL - Expressions
- SQL - Comments
- SQL Database
- SQL - Create Database
- SQL - Drop Database
- SQL - Select Database
- SQL - Rename Database
- SQL - Show Databases
- SQL - Backup Database
- SQL Table
- SQL - Create Table
- SQL - Show Tables
- SQL - Rename Table
- SQL - Truncate Table
- SQL - Clone Tables
- SQL - Temporary Tables
- SQL - Alter Tables
- SQL - Drop Table
- SQL - Delete Table
- SQL - Constraints
- SQL Queries
- SQL - Insert Query
- SQL - Select Query
- SQL - Select Into
- SQL - Insert Into Select
- SQL - Update Query
- SQL - Delete Query
- SQL - Sorting Results
- SQL Views
- SQL - Create Views
- SQL - Update Views
- SQL - Drop Views
- SQL - Rename Views
- SQL Operators and Clauses
- SQL - Where Clause
- SQL - Top Clause
- SQL - Distinct Clause
- SQL - Order By Clause
- SQL - Group By Clause
- SQL - Having Clause
- SQL - AND & OR
- SQL - BOOLEAN (BIT) Operator
- SQL - LIKE Operator
- SQL - IN Operator
- SQL - ANY, ALL Operators
- SQL - EXISTS Operator
- SQL - CASE
- SQL - NOT Operator
- SQL - NOT EQUAL
- SQL - IS NULL
- SQL - IS NOT NULL
- SQL - NOT NULL
- SQL - BETWEEN Operator
- SQL - UNION Operator
- SQL - UNION vs UNION ALL
- SQL - INTERSECT Operator
- SQL - EXCEPT Operator
- SQL - Aliases
- SQL Joins
- SQL - Using Joins
- SQL - Inner Join
- SQL - Left Join
- SQL - Right Join
- SQL - Cross Join
- SQL - Full Join
- SQL - Self Join
- SQL - Delete Join
- SQL - Update Join
- SQL - Left Join vs Right Join
- SQL - Union vs Join
- SQL Keys
- SQL - Unique Key
- SQL - Primary Key
- SQL - Foreign Key
- SQL - Composite Key
- SQL - Alternate Key
- SQL Indexes
- SQL - Indexes
- SQL - Create Index
- SQL - Drop Index
- SQL - Show Indexes
- SQL - Unique Index
- SQL - Clustered Index
- SQL - Non-Clustered Index
- Advanced SQL
- SQL - Wildcards
- SQL - Injection
- SQL - Hosting
- SQL - Min & Max
- SQL - Null Functions
- SQL - Check Constraint
- SQL - Default Constraint
- SQL - Stored Procedures
- SQL - NULL Values
- SQL - Transactions
- SQL - Sub Queries
- SQL - Handling Duplicates
- SQL - Using Sequences
- SQL - Auto Increment
- SQL - Date & Time
- SQL - Cursors
- SQL - Common Table Expression
- SQL - Group By vs Order By
- SQL - IN vs EXISTS
- SQL - Database Tuning
- SQL Function Reference
- SQL - Date Functions
- SQL - String Functions
- SQL - Aggregate Functions
- SQL - Numeric Functions
- SQL - Text & Image Functions
- SQL - Statistical Functions
- SQL - Logical Functions
- SQL - Cursor Functions
- SQL - JSON Functions
- SQL - Conversion Functions
- SQL - Datatype Functions
- SQL Useful Resources
- SQL - Questions and Answers
- SQL - Cheatsheet
- SQL - Quick Guide
- SQL - Useful Functions
- SQL - Useful Resources
- SQL - Discussion
SQL - BETWEEN Operator
- The SQL BETWEEN Operator
- SQL BETWEEN with Numeric Values
- SQL BETWEEN with Text Values
- SQL BETWEEN with Date Values
- SQL BETWEEN with IN Operator
- SQL BETWEEN with UPDATE Statement
- SQL BETWEEN with DELETE Statement
- SQL NOT BETWEEN Operator
- SQL NOT BETWEEN with Text Values
- SQL NOT BETWEEN with IN 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:
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
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 |
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:
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
2 | Khilan | 25 | Delhi | 1500.00 |
3 | Kaushik | 23 | Kota | 2000.00 |
4 | Chaitali | 25 | Mumbai | 6500.00 |
6 | Komal | 22 | Hyderabad | 4500.00 |
7 | Muffy | 24 | Indore | 10000.00 |
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:
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
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 |
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:
ORDER_ID | CUSTOMER_NAME | ORDER_DATE | AMOUNT |
---|---|---|---|
2 | Khilan | 2025-03-12 | 150.00 |
3 | Kaushik | 2025-05-20 | 300.00 |
4 | Chaitali | 2025-07-15 | 650.00 |
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:
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
5 | Hardik | 27 | Bhopal | 8500.00 |
6 | Komal | 22 | Hyderabad | 4500.00 |
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:
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
1 | Ramesh | 32 | Ahmedabad | 2000.00 |
2 | Khilan | 25 | Delhi | 10000.00 |
3 | Kaushik | 23 | Kota | 2000.00 |
4 | Chaitali | 25 | Mumbai | 10000.00 |
5 | Hardik | 27 | Bhopal | 10000.00 |
6 | Komal | 22 | Hyderabad | 4500.00 |
7 | Muffy | 24 | Indore | 10000.00 |
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:
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
1 | Ramesh | 32 | Ahmedabad | 2000.00 |
2 | Khilan | 25 | Delhi | 1500.00 |
4 | Chaitali | 25 | Mumbai | 6500.00 |
5 | Hardik | 27 | Bhopal | 8500.00 |
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:
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
1 | Ramesh | 32 | Ahmedabad | 2000.00 |
3 | Kaushik | 23 | Kota | 2000.00 |
6 | Komal | 22 | Hyderabad | 4500.00 |
7 | Muffy | 24 | Indore | 10000.00 |
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:
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
1 | Ramesh | 32 | Ahmedabad | 2000.00 |
7 | Muffy | 24 | Indore | 10000.00 |
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:
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
4 | Chaitali | 25 | Mumbai | 6500.00 |
7 | Muffy | 24 | Indore | 10000.00 |