The BETWEEN operator in PostgreSQL is used to filter the result set by selecting values within a given range. It can be applied to numeric, date, and even text values.

Syntax

column_name BETWEEN lower_bound AND upper_bound;

• Inclusive: The BETWEEN operator includes both boundary values.
• Equivalent To:

  column_name >= lower_bound AND column_name <= upper_bound;
  

Example Usage

1. Using BETWEEN with Numeric Values

SELECT * FROM employeesWHERE salary BETWEEN 30000 AND 60000;

✅ This retrieves all employees whose salary is between 30,000 and 60,000 (inclusive).

2. Using BETWEEN with Dates

SELECT * FROM ordersWHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

✅ This fetches all orders placed in the year 2024.

3. Using BETWEEN with Text (Alphabetical Order)

SELECT * FROM customersWHERE last_name BETWEEN 'A' AND 'M';

✅ This selects customers whose last names start from 'A' to 'M'.

Using NOT BETWEEN

To exclude a range:

SELECT * FROM employeesWHERE salary NOT BETWEEN 30000 AND 60000;