BETWEEN for range filtering in PostgreSQL

Introduction

BETWEEN helps you find values that fall within a certain range, making it easy to filter data between two points.

Finding all sales made between two dates.

Selecting students with scores between 50 and 80.

Getting products priced between $10 and $50.

Filtering events happening between two times.

Syntax

PostgreSQL

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

BETWEEN includes both value1 and value2 in the results.

Works with numbers, dates, and text (alphabetical order).

Examples

Finds all orders placed in January 2023.

PostgreSQL

SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';

Selects students with scores from 60 to 80, inclusive.

PostgreSQL

SELECT name, score FROM students WHERE score BETWEEN 60 AND 80;

Lists products priced between $10 and $50.

PostgreSQL

SELECT product_name FROM products WHERE price BETWEEN 10 AND 50;

Sample Program

This creates a table of employees with salaries, then selects those earning between $60,000 and $80,000.

PostgreSQL

CREATE TABLE employees (id SERIAL PRIMARY KEY, name TEXT, salary INTEGER);
INSERT INTO employees (name, salary) VALUES
('Alice', 50000),
('Bob', 70000),
('Charlie', 60000),
('Diana', 80000);

SELECT name, salary FROM employees WHERE salary BETWEEN 60000 AND 80000 ORDER BY salary;

Important Notes

BETWEEN is inclusive: it includes the start and end values in the results.

For dates, use the format 'YYYY-MM-DD' or your database's date format.

You can use NOT BETWEEN to exclude a range.

Summary

BETWEEN filters data within a range including both ends.

It works with numbers, dates, and text.

Use it to simplify range queries instead of multiple conditions.