MySQL BETWEEN
Filter data within a specific range
🔍 What is BETWEEN?
The BETWEEN operator selects values within a given range. It's inclusive, meaning it includes the start and end values. BETWEEN works with numbers, text, and dates.
-- Select products with price between 10 and 50
SELECT * FROM products
WHERE price BETWEEN 10 AND 50;
Output:
| id | name | price |
|---|---|---|
| 2 | Keyboard | 25.00 |
| 3 | Monitor | 45.00 |
Key BETWEEN Concepts
Numeric Range
Filter numbers within a range
WHERE age BETWEEN 18 AND 65Date Range
Select dates within a period
WHERE date BETWEEN '2024-01-01' AND '2024-12-31'Text Range
Filter alphabetically ordered text
WHERE name BETWEEN 'A' AND 'M'NOT BETWEEN
Exclude values in a range
WHERE price NOT BETWEEN 10 AND 50🔹 BETWEEN with Numbers
The BETWEEN operator is commonly used with numeric values to filter records within a specific numeric range. It's perfect for filtering prices, ages, quantities, or any numerical data. The operator includes both boundary values in the result set.
-- Find employees with salary between 30000 and 60000
SELECT employee_id, name, salary
FROM employees
WHERE salary BETWEEN 30000 AND 60000
ORDER BY salary;Output:
| employee_id | name | salary |
|---|---|---|
| 101 | John Smith | 35000 |
| 102 | Sarah Johnson | 48000 |
| 103 | Mike Davis | 55000 |
🔹 BETWEEN with Dates
Using BETWEEN with dates allows you to filter records within a specific time period. This is extremely useful for generating reports, analyzing trends, or finding records created or modified within certain dates. Always use proper date format (YYYY-MM-DD) for consistency.
-- 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';Output:
| order_id | customer_name | order_date |
|---|---|---|
| 1001 | Alice Brown | 2024-01-05 |
| 1002 | Bob Wilson | 2024-01-15 |
🔹 NOT BETWEEN
The NOT BETWEEN operator excludes values within the specified range and returns all other records. This is useful when you want to find outliers or records that fall outside a normal range. It's the opposite of BETWEEN and equally powerful for data filtering.
-- Find products NOT in the mid-price range
SELECT product_name, price
FROM products
WHERE price NOT BETWEEN 20 AND 80;Output:
| product_name | price |
|---|---|
| Budget Mouse | 12.99 |
| Premium Laptop | 999.99 |
🔹 BETWEEN with Text
BETWEEN works with text values using alphabetical ordering. This allows you to filter names, categories, or any text data that falls within an alphabetical range. Remember that text comparison is case-sensitive in some database configurations, so results may vary based on your settings.
-- Find customers with names starting from A to M
SELECT customer_id, name
FROM customers
WHERE name BETWEEN 'A' AND 'N'
ORDER BY name;Output:
| customer_id | name |
|---|---|
| 1 | Alice Cooper |
| 2 | Bob Martin |
| 3 | Mary Johnson |