BETWEEN operator

BETWEEN performs a Boolean True/False test to determine whether or not a value falls within a particular range, including the values mentioned in the BETWEEN clause.  BETWEEN returns TRUE when in the range, FALSE when not in the range, and NULL if any range values are unknown. It can’t be used with data type like BLOB, CLOB, NCLOB, REF, and ARRAY.

Syntax

SELECT …

WHERE expression [NOT] BETWEEN bottom_of_range AND top_of_range

Explained

WHERE expression

Compares an expression, that is, a single value like a column value or a literal expression, to the values within top_of_range and bottom_of_range. The addition of the optional [NOT] keyword tells the statement to look outside the values of that particular range.

For example, if we want to find employees with a hire_date between 01-Jan-2010 and 31-Dec-2013:

SELECT last_name, first_name, emp_ID

FROM employees

WHERE hire_date BETWEEN '01/01/2010' AND '31/12/2013';

To search for values outside of the BETWEEN range, just use the NOT keyword.  For example, if you want to see employees hired outside of 2013:

SELECT last_name, first_name, emp_ID

FROM employees

WHERE hire_date NOT BETWEEN '01/01/2013' AND '31/12/2013';

Advice

Since the AND keyword is used WHERE and JOIN clauses to compound conditions, many SQL developers surround the BETWEEN clause with parenthesis for better isolation and readability:

SELECT last_name, first_name, emp_ID

FROM employees

WHERE hire_date NOT BETWEEN ('01/01/2013' AND '31/12/2013')
  AND status = 'A';

Related Entries

ALL | ANY/SOME

EXISTS

IN

OPERATORS

SELECT

SOME

WHERE

Google Author