Oracle BETWEEN Operator

This SQL tutorial focuses on the Oracle BETWEEN operator, and provides explanations, examples and exercises. For this lesson’s exercises, use this link.

This tutorial is a part of several posts explaining how to use the WHERE clause in Oracle. To read additional posts regarding this subject, please use the following links:

• Introduction – Oracle WHERE clause
• Simple comparison operators
• IN operator
• BETWEEN operator
• LIKE operator
• IS NULL operator
• AND & OR operators

Oracle BETWEEN Operator

The Oracle BETWEEN operator is used to retrieve values based on a certain range.

WHERE column_name BETWEEN  lower_value AND upper_value

BETWEEN Operator with Numeric Values

This Oracle BETWEEN example would retrieve all products whose price range is between 50 and 80:

SELECT product_id, product_name, unit_price
FROM products
WHERE unit_price BETWEEN 50 AND 80

BETWEEN Operator with Character Values

SELECT product_name
FROM products
WHERE  product_name BETWEEN 'A' AND 'G'

• The string values must be enclosed within single quotes (‘string’)
• In oracle string values are case-sensitive. The actual value in the column must match the value you specify in the BETWEEN condition. For example, if you specify the letter range A-G and there is a product whose name equals “apple” (first letter is in lowercase), the name of this product will not be retrieved.

BETWEEN Operator with Date Values

 :This Oracle BETWEEN example would retrieve all customers whose birthdate is between 01.JAN.1990 and 01.JAN.2000

SELECT customer_id, first_name, birthdate
FROM customers
WHERE birthdate BETWEEN '01-JAN-1990' AND '01-JAN-2000'

• Date values must be enclosed within single quotes (‘date’)
• Date values are format sensitive.

Oracle NOT Operator

This operator is used to produce opposite results than those produced by the BETWEEN operator.

The NOT keyword must be written before the BETWEEN keyword.

This Oracle BETWEEN example would retrieve all products whose price is in the range between 50 and 80:

SELECT product_id, product_name, unit_price
FROM products
WHERE  unit_price BETWEEN 50 AND 80

This Oracle BETWEEN example would retrieve all products whose price is not in the range between 50 and 80:

SELECT product_id, product_name, unit_price
FROM products
WHERE  unit_price NOT BETWEEN 50 AND 80

General Guidelines

In an Oracle BETWEEN operator, the lower limit must be specified before the upper limit.

SELECT product_name, unit_price
FROM products
WHERE  unit_price BETWEEN 80 AND 20
-- No error will be generated and no rows will be returned either.

The Oracle BETWEEN operator is inclusive. For example, when you use the Oracle BETWEEN operator to retrieve the products whose price is in the range between 50 and 90, the result retrieves all of these products, including those whose price equals 50 or 90.

SELECT product_id, product_name, unit_price
FROM products
WHERE  unit_price BETWEEN 50 AND 90
-- All products whose price is in the range of 50 and 90
-- Including those whose price equals 50 or 90