SQL BETWEEN function: A guide with examples

The BETWEEN operator selects rows where a particular column lies within a range. It filters text, numbers, and date values. Dive into this guide to unravel the power of the SQL BETWEEN function and supercharge your SQL queries.

Understanding the SQL BETWEEN function

Identifying the right SQL range constraints to use is like finding a needle in a haystack. For the most part, you need multiple conditions to check if a value falls between two bounds. BETWEEN reduces this complexity with a touch of clarity and improved performance. A single, concise BETWEEN query sets a range of values and finds rows within that range.

A successful BETWEEN query has inclusive behavior. This means the results include the rows with values equal to the start and end of the endpoints. A good example is extracting products and finding items with prices between $10.35 and $18.75. The result set includes both $10.35 and $18.75.

Syntax and usage

The basic syntax of the BETWEEN function is as follows:

SELECT column_list

FROM data_table

WHERE column_name BETWEEN lower_value AND upper_value;

The lower_value is the lower bound, and the upper_value is the upper bound of the range. Both lower_value and upper_value set the inclusive range. The syntax should reflect as such when applied in a typical query:

SELECT first_name, last_name, salary -- columns

FROM employees -- Table

WHERE salary BETWEEN 2800 AND 3000; -- BETWEEN range

Practical examples of using BETWEEN in Oracle SQL

The BETWEEN operator is compatible with numeric, date queries, and string matching. Let’s run some examples to understand how these three data types work. First, ensure you have some sample data loaded on your Oracle SQL. We will use Oracle sample data.

Numeric range filtering using BETWEEN

Numeric range filtering covers NUMBER, INTEGER, or FLOAT values like salaries, prices, or ages. We want to find employees whose EmployeeID falls between 180 and 205. Our BETWEEN query will constitute the following:

SELECT *

FROM employees

WHERE employee_id BETWEEN 180 AND 205;

Using BETWEEN with strings

String data types mostly contain VARCHAR2 and CHAR. Oracle uses lexicographical comparison when filtering string values.

• A is less than B, B is less than C, and so on.
• Filtering BETWEEN ‘A’ AND ‘C’ includes all string values beginning with the letters A, B, and C.
• Oracle is case sensitive. BETWEEN ‘A’ AND ‘C’ will return only an uppercase result set.
• String length matters.

Consider the following example:

SELECT employee_id, first_name, last_name, salary

FROM employees

WHERE first_name BETWEEN 'A' AND 'C';

Oracle will show the result of employees whose first_name falls alphabetically between A and C. To counter Oracle’s case-sensitive behavior, use UPPER() or LOWER() functions:

SELECT *

FROM employees

WHERE UPPER(JOB_ID) BETWEEN 'G' AND 'P';

Date range queries with BETWEEN

The employee table contains the hire_date as follows:

Taking a closer look, you may consider adding date in your queries as follows:

SELECT hire_date

FROM employees

WHERE hire_date BETWEEN '07-JUN-94'

AND '07-JUN-96'

The above example treats these values as strings. To deal with dates, you should explicitly tell Oracle you’re dealing with a date range. Specify dates using TO_DATE/TO_TIMESTAMP and add the date formats:

SELECT hire_date

FROM employees

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

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

Note that if you have a full date (2025-01-10 13:40:44) and you don’t add the time part, Oracle assumes the default time as 00:00:00 (midnight) for that date. Otherwise, it’ll consider both the date and the time within the range.

Advanced concepts with BETWEEN in Oracle SQL

Some cases require you to use BETWEEN with more than one condition. This goes beyond simple filtering.

Logical operators combine multiple conditions to fine-tune filtering.

Combined use of BETWEEN with logical operators

Logical operators combine multiple conditions to fine-tune filtering. Examples of logical operators are AND, OR, and NOT. The syntax using the BETWEEN operator with the AND operator is as follows:
SELECT column_list
FROM data_table
WHERE column_name BETWEEN lower_value AND upper_value;
AND another_column = value;

Let’s filter employees who work in the sales department and have a salary between $6,000 and $8,000:
SELECT e.employee_id, e.first_name || ' ' || e.last_name AS Name, e.salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary BETWEEN 6000 AND 8000
AND d.department_name = 'Sales'

BETWEEN combined with OR filters multiple ranges where both ranges should be part of the result set:
SELECT e.employee_id, e.first_name || ' ' || e.last_name AS Name, e.salary
FROM employees e
WHERE (e.salary BETWEEN 4000 AND 6000)
OR (e.salary BETWEEN 8000 AND 10000);

Here, you can play with more than one lower and upper bound of a combined range. In other cases, you want to exclude a range within your result set. The NOT operator negates a BETWEEN condition and returns TRUE if the condition is false. NOT BETWEEN retrieves rows outside a range:
SELECT e.employee_id, e.first_name || ' ' || e.last_name AS Name, e.manager_id
FROM employees e
WHERE e.manager_id NOT BETWEEN 120 AND 130
AND e.manager_id NOT BETWEEN 140 AND 148;

BETWEEN doesn’t have to work exclusively with the WHERE clause. Extra clauses like HAVING and JOIN work perfectly. Consider this example. It uses JOIN and HAVING to return aggregated salaries of departments where the sum of employee salaries is between $10,000 and $800,000:
SELECT d.department_id, d.department_name, SUM(e.salary) AS total_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_id, d.department_name
HAVING SUM(e.salary) BETWEEN 10000 AND 800000;

Using BETWEEN in subqueries

Logical operators add more than two level conditions in a query. With subqueries, you can run a nested query. An inner query gets data that the main (outer) query needs to return the final result.
The BETWEEN operator works well with subqueries. The following example retrieves employees whose salary falls between the average and maximum salary:
SELECT * FROM employees
WHERE salary BETWEEN
(SELECT AVG(salary) FROM employees)
AND
(SELECT MAX(salary) FROM employees);

In this case, you have
● subquery (SELECT AVG(salary) FROM employees) to calculate a single value to represent the average salary;
● the (SELECT MAX(salary) FROM employees) subquery, which returns the value of the highest salary; and
● the work of BETWEEN checking the AVG and MAX values to output the employees within the range.
Large datasets can degrade query performance. Check these dynamic filtering queries and ensure they follow SQL performance characteristics.

Common mistakes and how to avoid them

There are some common mistakes when using SQL BETWEEN that may skew your query results. Let’s see how to avoid them.

BETWEEN is always inclusive unless specified differently

Misinterpretation of BETWEEN inclusivity

BETWEEN is always inclusive unless specified differently. Both boundary values are part of the output. Don’t assume BETWEEN excludes boundaries. If this is your designed output and you want to overlook some boundaries, consider operators like >, <, >= , or <= to create finer exclusivity queries. Here’s an example:
SELECT -------
WHERE manager_id > 140 AND manager_id < 180; -- Exclusive range
WHERE department_id >= 30 AND department_id < 100; -- Inclusive Lower and Exclusive Upper Bound
WHERE department_id > 100 AND department_id <= 200; -- Exclusive Lower and Inclusive Upper Bound

Incorrect range boundaries

The values in the BETWEEN clause are your range boundaries. Take a close look at how you order them. The first value is always the lower bound, followed by the upper bound.
SELECT -------
WHERE department_id BETWEEN 100 AND 90; ❌❌
WHERE department_id BETWEEN 90 AND 100; ✅✅

Be keen when working with datetime and floating-point ranges. They sometimes require precision boundaries. For example, BETWEEN 49.99 AND 100.00 won’t include 49.991 due to rounding off.

Data type mismatches

The values used with BETWEEN should have compatible data types. Query values should match the values and should have the column’s data type. If a column is an INTEGER, you must provide range values as INTEGER.
SELECT ——-

WHERE manager_id BETWEEN ‘100’ AND ‘108’; ❌❌
WHERE manager_id BETWEEN 100 AND 108; ✅✅

Keep in mind that BETWEEN doesn’t include NULL values. Its output excludes rows with NULL values.

Optimizing SQL queries with BETWEEN

However simple BETWEEN seems, you can always optimize your SQL query. Here are some tips:

• Use proper indexing to speed up the BETWEEN operator and the number of rows scanned. A BETWEEN query without an index does a full table scan, whereas an indexed one does only a range scan.

-- Query without an index
SELECT *
FROM employees
WHERE salary BETWEEN 4000 AND 6000;

-- Query with an index:
CREATE INDEX idx_salary ON employees(salary);
-- Scan only the rows within the salary range
SELECT *
FROM employees
WHERE salary BETWEEN 4000 AND 6000;

• Use alternatives to BETWEEN for complex queries with IN for discrete ranges and CASE for conditional ranges.
• Understand execution plans. You can use EXPLAIN PLAN to know how the database processes the BETWEEN query.

EXPLAIN PLAN FOR
SELECT employee_id, first_name, last_name
FROM employees
WHERE employee_id BETWEEN 200 AND 205;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY())

Conclusion

In this post, we dived deeper into understanding the BETWEEN operator. We can now simplify filtering data ranges and optimize BETWEEN for performance.

The key to solving problems in the simplest way possible lies in finding a solution that best uses the tools available. BETWEEN does exactly that. It transforms complex SQL filtering conditions into a single yet intuitive query.

With this newfound knowledge in hand, you’re now equipped to write cleaner SQL queries and use BETWEEN to take your SQL skills to the next level.

This post was written by Rose Chege. Rose is a technical writer, a lover of technology, and an upright individual unafraid to get out of her comfort zone and try new programming paradigms. She loves writing code and solving programming problems while sharing her skill using articles