Overview

Oracle SQL BETWEEN operator allows us to specify a range in the WHERE clause to fetch rows. We can use the BETWEEN AND operator in the WHERE clause.

Syntax

SELECT columns FROM table_name

WHERE  col_name BETWEEN <startexpr> AND <endexpr>;

To exclude the given range we can use the NOT operator.

SELECT columns FROM table_name

WHERE  col_name NOT BETWEEN <startexpr> AND <endexpr>;

Example

Given table

SQL> SELECT empno, ename, sal FROM employee;

     EMPNO ENAME                       SAL
---------- -------------------- ----------
      7369 SMITH                       800
      7499 ALLEN                      1600
      7521 WARD                       1250
      7566 JONES                      2975
      7654 MARTIN                     1250

We will write a query to find employees who earn salaries between 1000 and 2000.

SQL> SELECT empno, ename, sal FROM employee
WHERE sal BETWEEN 1000 AND 2000;

EMPNO     ENAME                  SAL
———-     ——————– ———-
7499           ALLEN                   1600
7521            WARD                    1250
7654           MARTIN                1250

 

—

Oracle Database Tutorials on this website:

https://www.testingdocs.com/oracle-database-tutorials-for-beginners/

More information about Oracle Database:

https://www.oracle.com/database/