SQL BETWEEN Operator

 The SQL BETWEEN operator is used to filter the result set within a specific range. It is often used in WHERE or HAVING clauses to specify a range for a column or expression. The range is inclusive, meaning it includes the values specified in the BETWEEN clause.

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

• value1 is the lower bound.
• value2 is the upper bound.
• Both bounds are included in the result.

Examples:

1. Numeric Range

SELECT *
FROM Employees
WHERE Salary BETWEEN 40000 AND 60000;

This query retrieves all employees whose salaries are between $40,000 and $60,000, inclusive.

2. Date Range

SELECT *
FROM Orders
WHERE OrderDate BETWEEN '2024-01-01' AND '2024-12-31';

This retrieves all orders placed within the year 2024.

3. Text Range (Lexicographical Order)

SELECT *
FROM Products
WHERE ProductName BETWEEN 'A' AND 'M';

This retrieves all products whose names start with letters between 'A' and 'M'.

4. NOT BETWEEN

To exclude a range, use NOT BETWEEN:

SELECT *
FROM Employees
WHERE Salary NOT BETWEEN 40000 AND 60000;

This retrieves employees whose salaries are outside the $40,000 to $60,000 range.

Notes:

• The BETWEEN operator works with numeric, date, and text data types.
• When using text values, the range comparison is case-insensitive in most databases, but this can depend on collation settings.
• Avoid using BETWEEN with floating-point numbers due to precision issues.