Introduction
Understanding how to query databases efficiently and accurately is paramount for anyone working with data. MySQL, one of the most popular relational database management systems, offers a plethora of operators to assist in fetching the data you need. One such operator is the BETWEEN operator. It simplifies the task of selecting values within a given range, inclusive of the range start and end points. In this tutorial, we will explore the ins and outs of using the BETWEEN operator in MySQL 8 through practical code examples.
The Fundamentals of BETWEEN Operator
The BETWEEN operator in MySQL is used to select values within a specific range. These values can be numbers, text, or dates. It is a shorthand for greater than or equal to (>=) and less than or equal to (<=). Therefore, when you use the BETWEEN operator, MySQL retrieves values that are within and including the limits specified.
Syntax:
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;Let’s see this in action. Suppose you have a database table named ‘products’ with a ‘price’ column. If you want to find products with a price ranging from $10 to $20, you would use:
SELECT *
FROM products
WHERE price BETWEEN 10 AND 20;
Basic Examples
Let’s begin with some basic examples that demonstrate the BETWEEN operator with various data types.
Example 1: Numeric Range
SELECT order_id, total_amount
FROM orders
WHERE total_amount BETWEEN 50 AND 100;
This query will return all orders with a total amount between $50 and $100.
Example 2: Date Range
SELECT order_id, order_date
FROM orders
WHERE order_date BETWEEN '2021-01-01' AND '2021-12-31';
This query selects all orders that were placed in the year 2021.
Example 3: Text Range
SELECT customer_id, first_name
FROM customers
WHERE first_name BETWEEN 'Alice' AND 'Bob';
It retrieves records of all customers with a first name that is alphabetically between ‘Alice’ and ‘Bob’.
Keep in mind that when using the BETWEEN operator with text values, the operation is case-insensitive in MySQL.
Advanced Examples
As we delve into more sophisticated examples, we will explore compound conditions, join operations, and the use of BETWEEN with subqueries.
Example 4: Compound Range Conditions
SELECT employee_id, salary, hire_date
FROM employees
WHERE salary BETWEEN 40000 AND 60000
AND hire_date BETWEEN '2015-01-01' AND '2020-12-31';
This query returns employees with a salary between $40,000 to $60,000 who were also hired between the years 2015 and 2020.
Example 5: Join Operations with BETWEEN
SELECT employees.name, departments.name, employees.hire_date
FROM employees
JOIN departments ON employees.dept_id = departments.id
WHERE employees.hire_date BETWEEN '2015-01-01' AND '2020-12-31';
You can join two tables and apply a BETWEEN condition on the result set. In this example, we’re fetching employees and their corresponding departments for hires between 2015 and 2020.
Example 6: Using BETWEEN with Subqueries
SELECT order_id, total_amount
FROM orders
WHERE total_amount BETWEEN (
SELECT MIN(total_amount)
FROM orders
WHERE year(order_date) = 2020
)
AND (
SELECT MAX(total_amount)
FROM orders
WHERE year(order_date) = 2020
);
This advanced example uses subqueries to define dynamic range endpoints based on the minimum and maximum total amount for orders placed in 2020.
Conclusion
Using the BETWEEN operator can make your queries simpler and more readable, especially when selecting data in a specified range. As we have seen from basic to advanced examples, BETWEEN is a versatile operator that can be used with numbers, dates, and text, as well as combined with joins and subqueries. Master this operator, and you will add a powerful tool to your SQL querying toolkit.
