Skip to main content
Documents
Basic SyntaxMath FunctionsDate FunctionsJSON FunctionsDatabasesTables & Schema ManagementString FunctionsTriggersIndexes

PostgreSQL BETWEEN

The `BETWEEN` operator in PostgreSQL is used to filter the result set within a specified range of values. It is inclusive, meaning both boundary values are considered part of the range.

Usage
The `BETWEEN` operator is typically used in `WHERE` clauses to select values within a specific range. It can be applied to numeric, text, and date data types.

SELECT column_name
FROM table_name
WHERE column_name BETWEEN lower_bound AND upper_bound;

In this syntax, `column_name` is checked to see if its values fall within `lower_bound` and `upper_bound`. Note that if `column_name` is `NULL`, the result of `BETWEEN` will also be `NULL`, effectively excluding those rows from the result set.

Examples

1. Basic Numeric Range

SELECT *
FROM products
WHERE price BETWEEN 10 AND 20;

This query retrieves all records from the `products` table where the `price` is between 10 and 20, inclusive.

2. Date Range

SELECT order_id, order_date
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

This example filters orders placed within the year 2023 by checking if `order_date` falls within the specified date range.

3. Text Range

SELECT employee_id, last_name
FROM employees
WHERE last_name BETWEEN 'A' AND 'M';

This query selects employees whose last names start with letters ranging from 'A' to 'M'. The range is determined by lexicographical ordering, which means the comparison is based on the alphabetical sequence of characters.

Tips and Best Practices

  • Inclusive Boundaries. Remember that `BETWEEN` includes the boundary values; if you need exclusive boundaries, consider using `>`, `<`, `>=`, or `<=`.
  • Consistent Data Types. Ensure that the data types of the column and the bounds are compatible to avoid errors.
  • Use with Date Functions. Combine `BETWEEN` with date functions like `CURRENT_DATE` for dynamic date ranges.
  • Consider Performance. For large datasets, ensure that the column used with `BETWEEN` is indexed to improve query performance.
  • Function Usage Caution. Be cautious when using functions on the column within the `BETWEEN` clause, as this can negate the benefits of indexing and impact performance.