MySQL BETWEEN

MySQL BETWEEN Operator

The BETWEEN operator in MySQL is used to filter rows with column values within a specified range, inclusive of the boundary values. It is often used with numeric, date, or even text data types.

Syntax

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

1. Basic Usage

Example

Retrieve employees with salaries between 40,000 and 60,000:

SELECT id, name, salary
FROM employees
WHERE salary BETWEEN 40000 AND 60000;

Result

+----+-------+--------+
| id | name  | salary |
+----+-------+--------+
| 1  | Alice | 45000  |
| 2  | Bob   | 55000  |
+----+-------+--------+

2. Using BETWEEN with Dates

Example

Get orders placed between January 1, 2024, and January 31, 2024:

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

3. Using BETWEEN with Text

The BETWEEN operator can also filter results based on lexicographical order (alphabetical order).

Example

Retrieve products whose names are alphabetically between "Laptop" and "Tablet":

SELECT product_id, product_name
FROM products
WHERE product_name BETWEEN 'Laptop' AND 'Tablet';

4. NOT BETWEEN

The NOT BETWEEN operator excludes rows that fall within the specified range.

Example

Find employees whose salaries are not between 40,000 and 60,000:

SELECT id, name, salary
FROM employees
WHERE salary NOT BETWEEN 40000 AND 60000;

5. Practical Example

Sample Table: products

+----+-------------+-----------+--------+
| id | product_name| category  | price  |
+----+-------------+-----------+--------+
| 1  | Laptop      | Tech      | 1500   |
| 2  | Monitor     | Display   | 300    |
| 3  | Smartphone  | Tech      | 800    |
| 4  | Tablet      | Tech      | 400    |
| 5  | Chair       | Furniture | 150    |
+----+-------------+-----------+--------+

Query: Get products priced between 300 and 1000:

SELECT product_name, price
FROM products
WHERE price BETWEEN 300 AND 1000;

Result:

+-------------+-------+
| product_name| price |
+-------------+-------+
| Monitor     | 300   |
| Smartphone  | 800   |
| Tablet      | 400   |
+-------------+-------+

6. Inclusive Nature of BETWEEN

The BETWEEN operator includes the boundary values (value1 and value2).

Example

Check for values exactly at the boundaries:

SELECT product_name, price
FROM products
WHERE price BETWEEN 150 AND 1500;

This query includes rows where the price is 150 and 1500.

7. Common Errors

1. Order of Values:
   The first value in the BETWEEN clause must be less than or equal to the second. Swapping values will result in no rows returned.

   -- Correct
   SELECT * FROM products WHERE price BETWEEN 300 AND 1000;
   
   -- Incorrect (returns no rows)
   SELECT * FROM products WHERE price BETWEEN 1000 AND 300;
   

2. NULL Values:
   Columns with NULL values do not match any BETWEEN condition unless explicitly handled.

8. Performance Considerations

• Indexing: Columns used in BETWEEN conditions should be indexed for optimal performance.
• Inclusive Boundaries: Ensure the range values are accurate to include all desired results.

9. Alternatives to BETWEEN

For more flexibility, you can use comparison operators (>= and <=) instead of BETWEEN.

-- Using BETWEEN
SELECT * FROM employees WHERE salary BETWEEN 40000 AND 60000;

-- Using comparison operators
SELECT * FROM employees WHERE salary >= 40000 AND salary <= 60000;

Both queries produce the same result, but BETWEEN is often more concise.

Let me know if you'd like further assistance or additional examples!