Introduction
The BETWEEN and NOT BETWEEN operators in PostgreSQL are powerful tools for filtering results within a specified range. This tutorial examines how to leverage these operators to control data queries effectively.
Understanding BETWEEN
BETWEEN helps in fetching data that falls within two values, inclusively. It’s commonly used with dates, numbers, and text fields when you need to cover a range of records. Here’s a simple example:
SELECT *
FROM orders
WHERE order_date BETWEEN '2021-01-01' AND '2021-12-31';
This query retrieves all orders placed in the year 2021. Notice that the inclusive nature of BETWEEN includes dates January 1, 2021, and December 31, 2021, in the results.
Employing NOT BETWEEN
Conversely, NOT BETWEEN selects rows that do not fall within the specified range. For example:
SELECT *
FROM employees
WHERE salary NOT BETWEEN 40000 AND 60000;
This query shows all employees whose salaries are either below $40,000 or above $60,000, excluding those within the range.
BETWEEN with Text Data
BETWEEN can also be used with text data, which is particularly handy when you need to select records based on alphabetically sorted information. Here’s how:
SELECT *
FROM products
WHERE name BETWEEN 'A' AND 'M';
The above statement returns products whose names begin with any letter from A to M, inclusive.
Advanced Date Range Filtering
When working with dates, BETWEEN proves indispensable. The following query uses INTERVAL to include results for the current month:
SELECT *
FROM events
WHERE event_date BETWEEN
CURRENT_DATE - INTERVAL '1 MONTH' AND CURRENT_DATE;
This query fetches events that occurred within the last month up to the current date.
Using BETWEEN with Timestamps
Timestamps, combining dates and times, can also be filtered with BETWEEN. This query returns log entries within a specific one-hour period on January 1st, 2021:
SELECT *
FROM log
WHERE timestamp BETWEEN
'2021-01-01 12:00:00' AND '2021-01-01 13:00:00';
It’s essential when using timestamps to remember that the milliseconds are also included in the range comparisons.
BETWEEN with NULL Values
Be cautious when dealing with NULL values and ranges. For instance, suppose address records may have a NULL value for zip_code. The following query would exclude such records:
SELECT *
FROM addresses
WHERE zip_code BETWEEN 90000 AND 90100;
To handle this, consider INCLUDING or explicitly handling NULLs using the IS NULL operator.
BETWEEN and Query Optimization
Although using BETWEEN is intuitive, overextending the ranges without proper indexes can slow down performance. Ensure that your tables are indexed on the columns utilized within your BETWEEN clauses where possible.
NOT BETWEEN with Complex Ranges
Combining NOT BETWEEN with other conditions can fine-tune your record exclusion. For example:
SELECT *
FROM orders
WHERE total_amount NOT BETWEEN 100 AND 200
AND order_date >= '2021-01-01';
This will exclude records with total_amount between 100 and 200, but only for orders from 2021 onwards.
JOIN Operations and BETWEEN
You can also use BETWEEN and NOT BETWEEN in JOIN operations, effectively expanding your data range querying into joined tables. Here’s a typical scenario:
SELECT *
FROM employees e
JOIN salaries s ON e.employee_id = s.employee_id
WHERE s.salary NOT BETWEEN 50000 AND 100000;
This statement would return all employees along with their salaries, excluding those in the 50,000 to 100,000 salary range.
