SQL BETWEEN Operator

The SQL BETWEEN & AND operator selects a range of data between two values. You can also use the BETWEEN function with dates. The BETWEEN function can also be combined with the NOT operator.

SQL BETWEEN SyntaxSELECT field(s) FROM Table1 WHERE Column1 BETWEEN Value1 AND Value2; 

This will select all rows whose fields has a value between ‘value1’ and ‘value2’. To illustrate the usage of the SQL BETWEEN command we are going to use the EMP table.

SELECT * FROM emp WHERE sal BETWEEN 1000 AND 3000; 

This would return all rows where the sal is between 1000 and 3000, inclusive. It is equivalent to the following SQL statement:

You can also use the BETWEEN function with dates. Now we want to select the records with a hiredate between “01-JAN-1980”and 31-DEC-1980. . To illustrate the usage of the SQL BETWEEN command we are going to use the EMP table.

SELECT * FROM emp WHERE hiredate between to_date (‘1980/01/01’, ‘yyyy/mm/dd’) AND to_date (‘1980/12/31’, ‘yyyy/mm/dd’);

The BETWEEN function can also be combined with the NOT operator. Now we want to exclude the records with a hiredate between “01-JAN-1980”and 31-DEC-1980. To illustrate the usage of the SQL NOT BETWEEN command we are going to use the EMP table.

SELECT * FROM emp WHERE hiredate NOT between to_date (‘1980/01/01’, ‘yyyy/mm/dd’) AND to_date (‘1980/12/31’, ‘yyyy/mm/dd’);