Between Operator in SQL

Between Operator in SQL is used to filter out the output within a specified range specified in the WHERE condition.
It can be used with SELECT, INSERT,UPDATE or DELETE SQL statements.

Syntax :

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

• In the above Syntax , the <value1> , <value2> can either be text, numbers or dates.
• The <column name>, <value1>, <value2> …all should be of same datatype. If they are of different
• Datatypes then oracle does an implicit conversion. If it cannot, then it throws an error.
• In Oracle database , the output is inclusive of the value 1 and value2 along with the values that fall between them. For e.g if range is given as BETWEEN 2000 and 3000, then the result will include all the values between them along with 2000 and 3000 themselves.

Sample Database :

Illustration of ‘BETWEEN’ using Numeric Values.

Note : The order of the numeric range should always be from Lower to Higher and not from Higher to Lower.

The query is example 1 is equivalent to :

SELECT * FROM EMP WHERE SAL >=2450 AND SAL <=3000;
Similar is the case with other examples.

Illustration of ‘BETWEEN’ using Date Values and Text Values.

Note : The Text and the Date values should be given in single quotes . Moreover values are case – sensitive. They should be given in proper case.

Illustration of ‘NOT BETWEEN’ using Number values.

Illustration of ‘NOT BETWEEN’ using Date Values.

Illustration of ‘NOT BETWEEN’ using Text Values.