MySQL BETWEEN Operator

  1. Overview
  2. MYSQL
  3. MySQL SQL
  4. MySQL BETWEEN Operator

The BETWEEN operator in MySQL is used to filter the results based on a range of values. It allows you to select values that fall within a given range, which can be numbers, dates, or text values.

Key Points:

  • The BETWEEN operator is inclusive, meaning that the start and end values of the range are included in the result.
  • It can be used with numeric, date, or text columns.
  • You can also use the NOT BETWEEN operator to exclude records that fall within a specific range.

BETWEEN Syntax

The basic syntax for using the BETWEEN operator is:

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Here:

  • value1 is the lower bound of the range.
  • value2 is the upper bound of the range.

Using BETWEEN with Numeric Values

Example: Select customers who made purchases within a specific price range.

SELECT ProductName, Price
FROM Products
WHERE Price BETWEEN 10 AND 50;
  • Explanation: This query retrieves all products with a price between 10 and 50, including the boundaries of 10 and 50.

Using BETWEEN with Date Values

You can also use the BETWEEN operator with dates. This is helpful for filtering records within a specific time range.

Example: Select orders placed between two dates:

SELECT OrderID, OrderDate
FROM Orders
WHERE OrderDate BETWEEN '2024-01-01' AND '2024-12-31';
  • Explanation: This query selects all orders placed between January 1, 2024, and December 31, 2024, including both dates.

NOT BETWEEN Operator

The NOT BETWEEN operator is used to select values that do not fall within a given range.

NOT BETWEEN Text Values Example

Here is an example using NOT BETWEEN with text values:

SELECT * FROM Products
WHERE ProductName NOT BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;
  • Explanation: This query selects all products where the product name is not alphabetically between "Carnarvon Tigers" and "Mozzarella di Giovanni" (inclusive). It excludes products whose names fall within this range.

BETWEEN Operator with Text Values

You can also use BETWEEN with text (string) values. The string values are compared alphabetically.

Example: Select customers whose names fall between two specific names:

SELECT CustomerName
FROM Customers
WHERE CustomerName BETWEEN 'C' AND 'M';
  • Explanation: This query retrieves customers whose names start with any letter between 'C' and 'M', including those starting with 'C' and 'M'.

Conclusion

The BETWEEN operator is versatile and useful for filtering data within a specific range. Whether you're working with numbers, dates, or text, the BETWEEN operator simplifies querying a range of values. Additionally, NOT BETWEEN allows you to exclude values in a specific range, providing flexibility for your data retrieval needs.

Was this article helpful?