BETWEEN Operator

The BETWEEN operator in Oracle SQL is used to filter records within a specific range. It allows you to retrieve rows where the values of a specified column are within a given range (inclusive). The BETWEEN operator can be applied to numerical, date, and string columns.

 

1. Basic Syntax of BETWEEN Operator

The basic syntax for the BETWEEN operator is:

SELECT column1, column2, ...

FROM table_name

WHERE column_name BETWEEN value1 AND value2;

• column_name: The column to be filtered.
• value1 and value2: The lower and upper bounds of the range. The range is inclusive, meaning both the value1 and value2 values are included in the result set.

 

2. Using BETWEEN with Numerical Values

You can use the BETWEEN operator to filter rows within a range of numbers. This is commonly used to filter records based on values such as salary, age, or any other numerical field.

Example:

SELECT * FROM employees

WHERE salary BETWEEN 50000 AND 100000;

This query will return all employees whose salary is between 50,000 and 100,000 (inclusive).

 

3. Using BETWEEN with Date Values

The BETWEEN operator can also be used with date columns to filter rows that fall within a specific range of dates. When working with dates, make sure the date format is correct, especially when using TO_DATE for date literals.

Example:

SELECT * FROM employees

WHERE hire_date BETWEEN TO_DATE('2020-01-01', 'YYYY-MM-DD')

                    AND TO_DATE('2021-01-01', 'YYYY-MM-DD');

This query will return all employees who were hired between January 1, 2020, and January 1, 2021 (inclusive).

 

4. Using BETWEEN with String Values

The BETWEEN operator can also be used with string (or character) values. Oracle compares the strings lexicographically (i.e., based on their alphabetical order).

Example:

SELECT * FROM employees

WHERE last_name BETWEEN 'A' AND 'M';

This query will return all employees whose last name starts with a letter between 'A' and 'M' (inclusive). Oracle compares the characters based on their alphabetical order.

 

5. BETWEEN Operator is Inclusive

One of the key characteristics of the BETWEEN operator is that it is inclusive, meaning both the lower and upper bounds are included in the result set. If you want to exclude one or both of the boundaries, you will need to use a different approach, such as > or < comparisons.

Example:

SELECT * FROM employees

WHERE salary BETWEEN 50000 AND 100000;

This query includes employees with a salary of exactly 50,000 and 100,000. If you want to exclude these values, you would use the following query:

SELECT * FROM employees

WHERE salary > 50000 AND salary < 100000;

 

6. BETWEEN Operator and Time

You can also use BETWEEN with time values. The time values are handled similarly to dates, and the format of the time should be consistent with Oracle's expectations (usually using the TO_TIMESTAMP function if necessary).

Example:

SELECT * FROM employees

WHERE hire_time BETWEEN TO_TIMESTAMP('08:00:00', 'HH24:MI:SS')

                   AND TO_TIMESTAMP('17:00:00', 'HH24:MI:SS');

This query will return all employees who were hired between 8:00 AM and 5:00 PM.

 

7. The BETWEEN Operator is Often Used with ORDER BY

The BETWEEN operator is often used in combination with ORDER BY to retrieve records within a specified range, while sorting them in a certain order (ascending or descending).

Example:

SELECT * FROM employees

WHERE salary BETWEEN 50000 AND 100000

ORDER BY hire_date DESC;

This query retrieves employees with salaries between 50,000 and 100,000 and sorts them by their hire date in descending order.

 

8. Performance Considerations with BETWEEN

Using BETWEEN with indexed columns (e.g., date, numerical, or string columns) can improve query performance, as Oracle can efficiently scan the relevant range of values. However, if the range is too broad or not selective enough, it could lead to a full table scan, which may reduce performance.

 

9. Using NOT BETWEEN

You can negate the BETWEEN operator by using NOT BETWEEN to filter values outside a given range.

Example:

SELECT * FROM employees

WHERE salary NOT BETWEEN 50000 AND 100000;

This query will return all employees whose salary is not between 50,000 and 100,000.

 

10. BETWEEN vs. >= and <=

While the BETWEEN operator is often used to filter a range, you can achieve the same result by using the >= and <= operators. The advantage of BETWEEN is that it provides cleaner, more readable code.

Example with BETWEEN:

SELECT * FROM employees

WHERE salary BETWEEN 50000 AND 100000;

Equivalent example using >= and <=:

SELECT * FROM employees

WHERE salary >= 50000 AND salary <= 100000;

Both queries will return the same results, but the BETWEEN version is more concise.

 

11. Common Use Cases for the BETWEEN Operator

Here are a few common scenarios where you might use the BETWEEN operator:

• Filtering based on a range of dates (e.g., employees hired between two dates).
• Filtering numerical values (e.g., retrieving employees with a salary in a specified range).
• Range comparisons in string fields (e.g., finding employees whose names start with certain letters).

Example for a Date Range:

SELECT * FROM orders

WHERE order_date BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD')

                    AND TO_DATE('2023-12-31', 'YYYY-MM-DD');

Example for a Numeric Range:

SELECT * FROM products

WHERE price BETWEEN 100 AND 500;

 

12. Tips for Using BETWEEN in Oracle SQL

• Date format consistency: When using the BETWEEN operator with dates, ensure that the format used matches Oracle's expected date format (e.g., using TO_DATE or TO_TIMESTAMP when necessary).
• Inclusive range: Remember that BETWEEN is inclusive of both the lower and upper bounds, so ensure this behavior is what you intend.
• Clarity and readability: For ranges where readability is important, using BETWEEN makes your queries clearer. However, for complex or exclusive ranges, you may prefer using explicit >= and <= operators.