SQL: BETWEEN CONDITION

SQL BETWEEN condition with examples and syntax.

DESCRIPTION

SQL BETWEEN Condition is using to retrieve values within the given range in a SELECT, INSERT, UPDATE, or DELETE statement.

SYNTAX

syntax for BETWEEN Condition using in SQL:

expression BETWEEN value1 AND value2;

PARAMETERS OR ARGUMENTS

expression is a column or calculation.

value1 and value2 create an inclusive range that expression is compared to.

-          The SQL BETWEEN Condition returns the records where expression is within the range from value1 to value2 (inclusive).

EXAMPLE - WITH NUMERIC

Below numeric example uses the BETWEEN condition to retrieve values within a numeric range.

For example:

SELECT * FROM employee WHERE age BETWEEN 22 AND 30;

This SQL BETWEEN example would return all rows where the age is between 22 and 30.

It is same as the following SQL SELECT statement:

SELECT * FROM employee WHERE age  >= 22 AND age  <= 30;

EXAMPLE - WITH DATE

You can use  BETWEEN condition with Dates also. The below  example uses the SQL BETWEEN condition to retrieve values within a date range.

For example:

SELECT *  FROM employee WHERE  Joining_date BETWEEN TO_DATE ('2010/01/01', 'yyyy/mm/dd') AND TO_DATE ('2012/12/31', 'yyyy/mm/dd');

EXAMPLE - USING NOT OPERATOR

The SQL BETWEEN condition can also be combined with the SQL NOT operator.

For example:

SELECT * FROM  Employee WHERE age NOT BETWEEN 25 AND 30;

SQL BETWEEN condition example would return all rows where the age is NOT between 25 and 30.

It is same as  following SQL SELECT statement:

SELECT * FROM Employee WHERE age  < 25 OR age  > 30;