The SQL BETWEEN operator is an invaluable tool for querying data within a range of values. By default, BETWEEN is inclusive, meaning it includes the start and end values. In this comprehensive guide, we‘ll cover everything you need to know about using BETWEEN in SQL queries.

What is the SQL BETWEEN Operator?

The BETWEEN operator allows you to filter result sets within a range of values. The basic syntax is:

SELECT column_name(s) 
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

This query will return all rows where column_name is greater than or equal to value1 and less than or equal to value2.

The BETWEEN operator is inclusive, meaning both the start and end values are included in the results. This differs from mathematical interval notation where square brackets [] indicate inclusion and parentheses ( ) indicate exclusion of endpoints.

For example, the following intervals are equivalent in SQL:

BETWEEN 1 AND 5 
[1, 5]

But the following have different meanings:

BETWEEN 1 AND 5 — Includes 1 and 5 
(1, 5) — Excludes 1 and 5

The inclusive nature of BETWEEN makes it convenient for filtering on ranges of values.

BETWEEN Uses Numeric, String, and Date Values

The BETWEEN operator works with numeric, string, and date data types. For strings, lexicographic ordering is used to determine inclusion within the range.

For example, here is a query that filters for strings starting with ‘b‘ through ‘f‘:

SELECT name 
FROM users
WHERE name BETWEEN ‘b‘ AND ‘fzz‘;

This follows alphabetic ordering, so ‘fzz‘ indicates the upper bound for strings starting with ‘f‘.

For dates, BETWEEN filters based on a date range. For example:

SELECT order_date, order_id  
FROM orders
WHERE order_date BETWEEN ‘2022-01-01‘ AND ‘2022-01-31‘;

This returns all orders placed in January 2022.

As you can see, BETWEEN works intuitively with various data types. Next let‘s go over some best practices for using BETWEEN.

BETWEEN Best Practices

Here are some guidelines for using BETWEEN effectively in SQL queries:

1. Use WITH Indexed Columns

BETWEEN will perform better when used on a column with an index, especially for large tables. The index allows quick searching within the sorted range.

2. Avoid Null Values

BETWEEN implicitly excludes null values. If you want to include them, use:

WHERE column BETWEEN 1 AND 5 OR column IS NULL

3. Use Inclusive Ranges

Inclusive ranges align best with the BETWEEN default behavior. Exclusive ranges can cause confusion or off-by-one errors.

4. Consistent Data Types

Use BETWEEN only for same-data-type comparisons. Implicit type conversions may cause unexpected results.

By following these tips, you can avoid pitfalls and effectively use the BETWEEN operator.

BETWEEN vs IN vs BETWEEN AND

In addition to BETWEEN, SQL offers the IN and BETWEEN AND operators for range queries. How do they compare?

IN Operator

The IN operator allows listing discrete values, like:

SELECT *
FROM products 
WHERE price IN (19.99, 29.99, 39.99);

IN takes an explicit allow list, while BETWEEN uses a range. IN can check membership across noncontiguous sets.

BETWEEN AND Operator

The BETWEEN AND operator has the same functional behavior as BETWEEN, but expands to be a bit more verbose:

SELECT * 
FROM products
WHERE price BETWEEN 19.99 AND 39.99;

-- Is equivalent to:  

SELECT *
FROM products  
WHERE price >= 19.99 AND price <= 39.99;

Some developers prefer BETWEEN AND for added clarity. But BETWEEN reads fine for most range queries.

So in summary:

  • IN: filters on a set of discrete values
  • BETWEEN: filters between an inclusive range
  • BETWEEN AND: alias for BETWEEN with > = and <=

Choose the operator that best matches your specific filtering criteria.

Handling Nulls with COALESCE

As mentioned earlier, the BETWEEN operator implicitly excludes null values. To include nulls in the range, you can use the COALESCE function:

SELECT *
FROM orders
WHERE 
  COALESCE(shipped_date, ‘1900-01-01‘) BETWEEN ‘2022-01-01‘ AND ‘2022-01-31‘

COALESCE returns the first non-null value. So for null shipped_date values, it substitutes a date guaranteed to be included in the range. This catches both existing and missing dates in the date range.

Dynamic Ranges with Subqueries

You can use subqueries to dynamically calculate start and end values for a BETWEEN range.

For example, this query gets the oldest and newest order dates and uses them to filter orders:

SELECT * 
FROM orders
WHERE order_date BETWEEN
  (SELECT MIN(order_date) FROM orders) AND  
  (SELECT MAX(order_date) FROM orders)

Here the subqueries dynamically fetch the min and max dates allowing filtering across the full range.

Subqueries enable parameterizing BETWEEN ranges based on database data.

BETWEEN Performance Considerations

The BETWEEN operator provides an efficient means of filtering result sets by a range of values. However, there are some performance considerations to keep in mind:

Table Scans

If no index exists on the target column, the database may perform a full table scan reading every row. Table scans can be very slow for large tables.

Function Calls

Using functions in a BETWEEN clause may hinder performance. For example: 

WHERE DAY(order_date) BETWEEN 1 AND 5

This calls the DAY() function against every row, preventing use of an index.

When possible, use BETWEEN only on direct column references. Or include the function condition elsewhere in the WHERE clause.

Row Estimation

Some databases estimate the number of rows a query will return. BETWEEN may lead to inaccurate estimates since the full range isn’t known. Be sure to check execution plans for overly optimistic estimates.

By keeping these factors in mind, you can optimize use of BETWEEN in performance critical queries.

Additional BETWEEN Usage Examples

Here are some additional examples demonstrating common usage patterns with the BETWEEN SQL operator:

1. Text Field Starts With Range

Filter for last names starting from ‘Li‘ through ‘Smith‘:

SELECT *
FROM customers
WHERE last_name BETWEEN ‘Li‘ AND ‘Smith‘;

2. Date Range by Year

Get orders from January through March of 2022:

SELECT * 
FROM orders  
WHERE order_date BETWEEN ‘2022-01-01‘ AND ‘2022-03-31‘;

3. Multi-Column Range

Select products priced between $50 and $100 with at least 5 units in stock:

SELECT *
FROM products
WHERE price BETWEEN 50 AND 100
  AND units_in_stock BETWEEN 5 AND 1000;

4. Negative Numbers

Filter results between -1000 through 500: 

SELECT * 
FROM order_details
WHERE discount BETWEEN -1000 AND 500;

Negative numbers pose no issues for BETWEEN in SQL.

As you can see, BETWEEN allows flexible range queries across data types like strings, dates, and numeric values.

Conclusion

The BETWEEN operator provides a concise way to filter SQL query results within a range of values. BETWEEN is inclusive by default, meaning both the start and end values are included. It works well with numeric, string, and date data types.

In summary, here are some key points about SQL BETWEEN:

  • Filters results within an inclusive range of values
  • Compatible with numeric, text, and date data types
  • Excludes NULL values (use COALESCE to include)
  • Has better performance on indexed columns
  • Watch for inaccurate row estimates or full table scans
  • Pairs nicely with other SQL clauses like WHERE and AND

By mastering the BETWEEN operator, you can write efficient range-based queries in SQL. BETWEEN helps reduce query complexity and provides an intuitive interface for filtering result sets.

So next time you need to query a range of database values, be sure to keep the BETWEEN operator handy!

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *