PostgreSQL – BETWEEN Clause
The BETWEEN operator is used to filter rows based on a range of values. It’s commonly used to retrieve rows where a particular column’s value falls within a specified range. The syntax of the BETWEEN operator is as follows:
SELECT column1, column2, ... FROM table_name WHERE column_name BETWEEN value1 AND value2;Here’s an example of using the BETWEEN operator:
Consider a table named sales with columns id, product, and amount. You want to retrieve all sales records with amount between $100 and $1000.
SELECT id, product, amount FROM sales WHERE amount BETWEEN 100 AND 1000;In this query, the BETWEEN operator checks whether the value in the amount column falls between 100 and 1000 (inclusive).
It’s important to note that the BETWEEN operator is inclusive, meaning that it includes both the lower and upper boundary values in the range. If you want to exclude one of the boundary values, you would need to use other comparison operators (< or >) in conjunction with the AND operator.
For example, if you want to retrieve sales records with amounts greater than $100 but less than $1000, excluding both $100 and $1000:
SELECT id, product, amount FROM sales WHERE amount > 100 AND amount < 1000;This query retrieves rows where the amount is greater than 100 and less than 1000, effectively excluding the boundaries.
The BETWEEN operator is a useful tool when you need to filter rows based on a specific range of values, and it can simplify your SQL queries by providing a concise way to express such conditions.