MySQL : BETWEEN Operator

In MySQL, the BETWEEN operator is used in the WHERE clause to select values within a specified range. It allows you to specify a range of values rather than using multiple >= and <= operators. Here's how it works:

Basic Syntax:


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

  • column1, column2, ...: The columns you want to retrieve data from.
  • table_name: The name of the table you want to retrieve data from.
  • column_name: The column you want to compare values against.
  • value1 and value2: The range of values. Both value1 and value2 are inclusive.

Example:


SELECT * FROM products WHERE price BETWEEN 10 AND 50;
 

This query will retrieve all products where the price is between $10 and $50.

Using with Dates:

The BETWEEN operator is commonly used with date values as well.

Example:

SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';
 

This query will retrieve all orders placed between January 1, 2024, and March 31, 2024.

Using NOT BETWEEN:

You can also use the NOT BETWEEN operator to select values outside a specified range.

Example:

SELECT * FROM products WHERE price NOT BETWEEN 10 AND 50;
 

This query will retrieve all products where the price is not between $10 and $50.

The BETWEEN operator provides a concise way to specify range-based conditions in your SQL queries, making them more readable and easier to understand.