PostgreSQL: Using the BETWEEN Condition for Range Queries

Efficiently query data within specific ranges using PostgreSQL's `BETWEEN` operator. This guide provides examples and syntax explanations.

Using the `BETWEEN` Condition in PostgreSQL

PostgreSQL's `BETWEEN` condition is used in the `WHERE` clause of SQL queries to select rows where a column's value falls within a specified range. It's a very useful tool for filtering data based on numerical or date ranges.

Understanding the `BETWEEN` Condition

The `BETWEEN` condition checks if a value is within a given range (inclusive). The syntax is:


column_name BETWEEN value1 AND value2;

This is equivalent to:


column_name >= value1 AND column_name <= value2;

The condition is `true` if the `column_name` value is greater than or equal to `value1` and less than or equal to `value2`.

Example 1: Numeric Range

This example selects customers whose age is between 23 and 26 (inclusive):

SELECT cust_id, cust_name, cust_age 
FROM customers
WHERE cust_age BETWEEN 23 AND 26
ORDER BY cust_age DESC;

    

Example 2: Date Range

This example selects employees who joined between two dates (inclusive). Dates should be in YYYY-MM-DD format.

SELECT emp_fname, dept_id, dept_name, joining_date
FROM department
WHERE joining_date BETWEEN '2017-10-12' AND '2020-06-22'
ORDER BY dept_id DESC;

    

Note: The `BETWEEN` operator is inclusive. The start and end values are included in the range.

Using `NOT BETWEEN`

To select values *outside* a range, use `NOT BETWEEN`:

SELECT cust_id, cust_name, cust_age 
FROM customers
WHERE cust_age NOT BETWEEN 23 AND 26
ORDER BY cust_age DESC;

    