BETWEEN Condition in Oracle SQL – PLSQL

The BETWEEN condition in Oracle SQL / PLSQL allows us to filter the records within a range of values including values provided for the range.
BETWEEN condition can be used with SELECT, INSERT, UPDATE and DELETE SQL statements.

Syntax for the BETWEEN condition in Oracle SQL / PLSQL is:

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

Or

INSERT INTO table_name
VALUES(column_name1
,colum_name2
,column_name3
.
.
column_nameN)
WHERE column_name BETWEEN value1 AND value2;

Or

UPDATE table_name
SET (column_name1 = value/expression
,column_name2 = value/expression
, column_name3 = value/expression
.
.
)
WHERE column_name BETWEEN value1 AND value2;

Or

DELETE FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Let’s take an example for understanding:
Suppose have a table named ‘employee’ in the database as shown below.

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Scenario 1:

Suppose we want to see the records of employee having Salary >= ‘20000’ and Salary <= '31000'. [sourcecode language="sql"] SELECT * FROM employee WHERE salary BETWEEN 20000 AND 31000; [/sourcecode] The result of the above query will be:

Employee_ID Employee_Name Salary Department Commission
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support

Here we have retrieved the records pertaining to employee having Salary more than ‘20000’ and less than ‘31000’ including the values provided in the range using BETWEEN condition along with WHERE clause.


Scenario 2:

We can use multiple BETWEEN conditions in a query.
Let’s assume that we have a new table namely ‘employee’ as shown below:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Suppose we want to view all the records of employees who are having Salary more than ‘20000’ and less than ‘31000’ and having commission more than ’15’ and less than ’25’ including values provided in range.

We can achieve the same as:

SELECT *
FROM employee
WHERE salary     BETWEEN 20000 AND 31000
AND   commission BETWEEN 15    AND 25;

Once we have run the above code following will be the result:

Employee_ID Employee_Name Salary Department Commission
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20

Here we have successfully retrieved the records pertaining to employees who are having Salary >= ‘20000’ and Salary <= '31000' and having commission more than '15' and less than '25' using BETWEEN condition in combination with WHERE clause.


Tagged , , , , , , , , , , , . Bookmark the permalink.