PostgreSQL - BETWEEN operator

In PostgreSQL, the BETWEEN operator is used to filter a range of values in a query. It's inclusive, meaning both the low and high values specified are included in the range.

Syntax:

expression BETWEEN value1 AND value2

expression can be a column name, arithmetic operation, or a function.
value1 is the low end of the range.
value2 is the high end of the range.

Examples:

Basic Usage with Numbers:
Let's say you have a table named products and you want to retrieve all products with a price between 10 and 50:
```
SELECT * FROM products WHERE price BETWEEN 10 AND 50;
```
Usage with Dates:
To get all orders placed between January 1, 2022, and December 31, 2022, from an orders table:
```
SELECT * FROM orders WHERE order_date BETWEEN '2022-01-01' AND '2022-12-31';
```
Usage with Strings:
If you want to retrieve all products with names lexicographically between 'Apple' and 'Mango' (inclusive):
```
SELECT * FROM products WHERE product_name BETWEEN 'Apple' AND 'Mango';
```
Using NOT BETWEEN:
The NOT BETWEEN operator retrieves values outside the specified range. If you want products outside the price range of 10 and 50:
```
SELECT * FROM products WHERE price NOT BETWEEN 10 AND 50;
```

Points to Note:

BETWEEN is inclusive, so both boundary values (value1 and value2) are included in the range.
When using BETWEEN with strings, PostgreSQL uses the lexicographic order, which is based on the ASCII values of characters. So, "Mango" comes before "apple" because uppercase letters have lower ASCII values than lowercase ones.
Always be cautious of potential performance implications, especially when working with large datasets. Using BETWEEN on indexed columns can help improve query performance.
When working with dates and times, be especially careful about time zones and the exact time you want to include or exclude. If you're working with timestamp fields and use BETWEEN '2022-01-01' AND '2022-12-31', for instance, you'd exclude any events on the night of December 31 after midnight, which might not be the intended behavior.

Using BETWEEN Operator in PostgreSQL queries:
- Description: The BETWEEN operator in PostgreSQL is used to filter results within a specified range. It's often used in conjunction with numerical, text, or date/time values.
- Code:
```
SELECT * FROM table_name
WHERE column_name BETWEEN value1 AND value2;
```
Range queries with BETWEEN Operator in PostgreSQL:
- Description: The BETWEEN operator is particularly useful for range queries. It selects values within a specified range, inclusive of the specified endpoints.
- Code:
```
SELECT * FROM products
WHERE price BETWEEN 50 AND 100;
```
Combining BETWEEN and AND in PostgreSQL:
- Description: The BETWEEN operator is often combined with the AND keyword to define both endpoints of a range explicitly.
- Code:
```
SELECT * FROM employees
WHERE age BETWEEN 25 AND 35;
```
Handling date and time ranges with BETWEEN in PostgreSQL:
- Description: BETWEEN can be used for date and time ranges as well. It's handy for selecting records with timestamps within a specified time frame.
- Code:
```
SELECT * FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';
```
Using NOT BETWEEN in PostgreSQL:
- Description: NOT BETWEEN is the negation of BETWEEN. It selects records outside the specified range.
- Code:
```
SELECT * FROM inventory
WHERE quantity NOT BETWEEN 10 AND 50;
```
Numeric range queries with BETWEEN in PostgreSQL:
- Description: BETWEEN is commonly used for numeric range queries, allowing you to filter records based on numeric values.
- Code:
```
SELECT * FROM grades
WHERE score BETWEEN 80 AND 100;
```
Optimizing queries with indexes and BETWEEN in PostgreSQL:
- Description: To optimize performance, consider indexing columns involved in BETWEEN queries, especially when dealing with large datasets. Indexing can significantly speed up range queries.
- Code (Indexing):
```
CREATE INDEX idx_price ON products(price);
```
```
SELECT * FROM products
WHERE price BETWEEN 50 AND 100;
```