MySQL BETWEEN

The MySQL BETWEEN operator is used to filter rows based on a range of values within a specified column. It is often used in the WHERE clause of a SQL query to retrieve rows where a particular column’s value falls within a specified range. The BETWEEN operator checks if a value is greater than or equal to a lower bound and less than or equal to an upper bound.

The basic syntax of the BETWEEN operator is as follows:

SELECT columns
FROM table
WHERE column_name BETWEEN lower_bound AND upper_bound;

Here’s how it works:

• column_name is the name of the column you want to check for values within the specified range.
• lower_bound is the lower limit of the range.
• upper_bound is the upper limit of the range.

Here are some examples to illustrate the use of the BETWEEN operator:

1. Retrieve all records where the unit_price falls within the range of $10 to $50:

   SELECT *
   FROM products
   WHERE unit_price BETWEEN 10 AND 50;

2. Match all orders with an order_date between ‘2023-01-01’ and ‘2023-12-31’:

   SELECT *
   FROM orders
   WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

3. Find employees with a salary between $40,000 and $60,000:

   SELECT *
   FROM employees
   WHERE salary BETWEEN 40000 AND 60000;

The BETWEEN operator is a convenient way to filter rows based on a specific range of values within a column. It’s particularly useful when you need to retrieve data that falls within certain bounds, such as date ranges, price ranges, or numerical value ranges.