PostgreSQL: BETWEEN Operator – Filter Data Within a Range

Welcome to The Coding College, your trusted platform for coding tutorials! In this guide, we’ll explore the BETWEEN operator in PostgreSQL, which simplifies range-based filtering in SQL queries.

What is the BETWEEN Operator?

The BETWEEN operator is used in SQL to filter data within a specific range. It works for numeric, date, and even textual data (based on lexicographical order).

Syntax

SELECT column_name
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

• value1: The starting value of the range (inclusive).
• value2: The ending value of the range (inclusive).

You can also use NOT BETWEEN to exclude values within the range.

Example: Sample Table

Consider a table named orders:

Using the BETWEEN Operator

Example 1: Filter Orders Within a Price Range

SELECT order_id, customer_name, total_amount
FROM orders
WHERE total_amount BETWEEN 200 AND 500;

Result:

Example 2: Find Orders Placed Within a Date Range

SELECT order_id, customer_name, order_date
FROM orders
WHERE order_date BETWEEN '2024-12-01' AND '2024-12-10';

Result:

Using NOT BETWEEN

Example 3: Exclude Orders Within a Specific Date Range

SELECT order_id, customer_name, order_date
FROM orders
WHERE order_date NOT BETWEEN '2024-12-01' AND '2024-12-10';

Result:

Combining BETWEEN with Other Conditions

Example 4: Orders Above $200 and Within a Date Range

SELECT order_id, customer_name, total_amount, order_date
FROM orders
WHERE total_amount > 200 AND order_date BETWEEN '2024-12-01' AND '2024-12-15';

Result:

Real-World Applications

1. Financial Analysis: Filter transactions within specific monetary ranges.
2. Date-Based Queries: Retrieve records for specific time periods (e.g., weekly, monthly reports).
3. Data Cleanup: Identify records with values outside acceptable ranges.

Learn More at The Coding College

Explore more PostgreSQL tutorials and coding insights at The Coding College. Our content adheres to Google’s E-E-A-T guidelines, ensuring expertise, authority, and trust.

Conclusion

The PostgreSQL BETWEEN operator is a straightforward yet powerful tool for range-based filtering. Whether you’re working with dates, numbers, or text, it simplifies your queries and makes data retrieval efficient.

Stay connected with The Coding College for more tutorials and tips to enhance your coding journey!