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 :

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.

between operator

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.

between operator

Illustration of ‘NOT BETWEEN’ using Number values.

Not Between

Illustration of ‘NOT BETWEEN’ using Date Values.

not between

Illustration of ‘NOT BETWEEN’ using Text Values.

Not Between


 

Print Friendly, PDF & Email
Pattern Matching in SQL - Using Wildcards
Shikha Katariya

Shikha Katariya

Shikha Katariya ,the Blog author is QA Engineer by profession,Currently serving in MNC, She has more than 4 years of experience in software industry and has worked for domains like Insurance , Core & retail Banking. Always keen to learn new technologies , she has working experience in mainframes,informatica ,and ETL Testing.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *