PostgreSQL BETWEEN

BETWEEN Operator Patterns

WHERE price BETWEEN 10 AND 100

WHERE order_date BETWEEN 
'2024-01-01' AND '2024-12-31'

WHERE name BETWEEN 'A' AND 'M'

WHERE age NOT BETWEEN 18 AND 65

🔹 Basic BETWEEN with Numbers

BETWEEN is inclusive, meaning it includes both the start and end values in the range. It's a shorthand for >= AND <=, making your queries cleaner and easier to read. Use it for filtering numeric data like prices, quantities, ages, or scores within specific boundaries.

-- Sample data: products table
-- product_id | product_name | price
-- 1          | Laptop       | 1200
-- 2          | Mouse        | 25
-- 3          | Keyboard     | 75
-- 4          | Monitor      | 300
-- 5          | Webcam       | 150

-- Find products priced between $50 and $500
SELECT product_name, price FROM products
WHERE price BETWEEN 50 AND 500;

🔹 BETWEEN vs Comparison Operators

BETWEEN is equivalent to using >= AND <= but more concise and readable. Both approaches produce identical results, but BETWEEN makes your intent clearer. When you need to check if a value falls within a range, BETWEEN is the preferred choice for code clarity and maintainability.

-- Using comparison operators (longer)
SELECT * FROM employees
WHERE salary >= 50000 AND salary <= 100000;

-- Using BETWEEN (cleaner)
SELECT * FROM employees
WHERE salary BETWEEN 50000 AND 100000;

🔹 BETWEEN with Dates

BETWEEN works excellently with date and timestamp columns for filtering records within time periods. Use it to find orders, events, or transactions within specific date ranges. Remember to use proper date format (YYYY-MM-DD) and that BETWEEN includes both start and end dates in the results.

-- Find orders placed in January 2024
SELECT order_id, customer_name, order_date FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';

🔹 BETWEEN with Text Values

BETWEEN can filter text data alphabetically, comparing strings character by character. It's useful for finding names or codes within alphabetical ranges. The comparison is case-sensitive by default. Text values are compared using alphabetical order, so 'A' comes before 'Z', and 'Apple' comes before 'Banana'.

-- Find customers with names starting from A to M
SELECT name FROM customers
WHERE name BETWEEN 'A' AND 'N'
ORDER BY name;

🔹 NOT BETWEEN Operator

Use NOT BETWEEN to find values outside a specified range. It returns all records where the value is less than the lower bound or greater than the upper bound. This is useful for excluding middle ranges, finding outliers, or filtering data that falls outside normal parameters.

-- Find products NOT in the mid-price range
SELECT product_name, price FROM products
WHERE price NOT BETWEEN 100 AND 500;

🔹 BETWEEN with Multiple Conditions

Combine BETWEEN with other WHERE conditions using AND or OR for complex filtering. You can use multiple BETWEEN clauses or mix BETWEEN with other operators. This allows sophisticated queries like finding products in a price range from specific categories or orders within date ranges from certain regions.

-- Find electronics priced between $200 and $1000
SELECT product_name, category, price FROM products
WHERE price BETWEEN 200 AND 1000
  AND category = 'Electronics';