PostgreSQL BETWEEN

PostgreSQL BETWEEN Operator

The BETWEEN operator selects values within a strictly given data range. The values can be numerical integers, textual text, or chronological dates.

It is highly inclusive, meaning the begin and end values are part of the result. It acts as a clean shorthand for using greater-than and less-than operators.

The Basic Syntax

The BETWEEN keyword is followed by the minimum value, an AND, and the maximum. It completely avoids writing out the targeted column name twice in the query.

This makes reading and reviewing SQL queries significantly faster for developers. The database engine inherently understands you are establishing a strict boundary.

-- Selects all products with a price ranging from 10 to 20
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;

The NOT BETWEEN Operator

To display data strictly outside of your specific range, use NOT BETWEEN. This actively filters out any row that naturally falls within the declared boundary.

It is commonly used to find statistical outliers or exceptional anomalies. The database will output anything exceptionally low or exceptionally high.

-- Selects products that are cheaper than 10 or more expensive than 20
SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;

BETWEEN with Text Values

The operator is not rigidly limited to numbers; it evaluates alphabetized strings. Using BETWEEN on text fields returns values alphabetically within the range.

However, textual boundaries can be tricky due to how databases evaluate characters. It often relies on the exact collation and strict character sets of the database.

-- Find products alphabetically between 'Carnarvon Tigers' and 'Mozzarella'
SELECT * FROM Products
WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;

BETWEEN with Dates

The absolute most popular use case for BETWEEN is filtering chronological date ranges. You can seamlessly extract records that occurred during a specific fiscal quarter.

Always ensure your provided dates are formatted safely as standard ISO strings. The standard format recognized heavily by PostgreSQL is 'YYYY-MM-DD'.

Summary

The BETWEEN operator simplifies range filtering logic effortlessly. It is highly inclusive and perfectly handles numbers, text, and dates cleanly.

Always format date strings accurately to guarantee flawless chronological results.