MySQL BETWEEN Range

The BETWEEN command is an operator in MySQL that is used to filter query results based on a range of values.

It is often used in the WHERE clause of a SQL statement to specify a range of values for a particular column.

The syntax for the BETWEEN operator is as follows: 

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

The BETWEEN operator checks if the value of column_name is within the range of value1 and value2, including the endpoints. If the value of column_name is within the range, it will be included in the query results.

For example, the following SQL statement retrieves all rows from the students table where the value of the age column is between 18 and 25:

SELECT * 
FROM students 
WHERE age BETWEEN 18 AND 25;

In this example, only the rows with an age value between 18 and 25, inclusive, will be returned by the query.

The BETWEEN operator can also be used with dates and times. 

For example, the following SQL statement retrieves all rows from the orders table where the order_date column is between January 1st, 2022 and December 31st, 2022: 

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

In this example, only the rows with an order_date between January 1st, 2022 and December 31st, 2022, inclusive, will be returned by the query.

Default listing:

mysql> SELECT * FROM friends;
+----+------+----------+-----------+
| id | Name | Lastname | Telephone |
+----+------+----------+-----------+
| 15 | Ana  | Smith    |    111222 |
| 16 | Ana  | Gatez    |    555666 |
| 17 | John | Snow     |    666777 |
| 18 | John | Smith    |    999888 |
+----+------+----------+-----------+
4 rows in set (0.00 sec)

mysql>

BETWEEN usage:

mysql> SELECT * FROM friends
    -> WHERE id BETWEEN 16 AND 18;
+----+------+----------+-----------+
| id | Name | Lastname | Telephone |
+----+------+----------+-----------+
| 16 | Ana  | Gatez    |    555666 |
| 17 | John | Snow     |    666777 |
| 18 | John | Smith    |    999888 |
+----+------+----------+-----------+
3 rows in set (0.00 sec)

mysql>

The WHERE clause in the query specifies the condition for the BETWEEN operator to filter the rows based on the id column.

The output shows the 3 rows that were returned by the query, along with their corresponding values for each column. Only the rows with an id value between 16 and 18, inclusive, were returned by the query.

NOT BETWEEN example:

mysql> SELECT * FROM friends
    -> WHERE id NOT BETWEEN 16 AND 17;
+----+------+----------+-----------+
| id | Name | Lastname | Telephone |
+----+------+----------+-----------+
| 15 | Ana  | Smith    |    111222 |
| 18 | John | Smith    |    999888 |
+----+------+----------+-----------+
2 rows in set (0.00 sec)

mysql>

Another example output of a MySQL query that uses the BETWEEN operator, but this time with the NOT keyword to retrieve rows from the friends table where the value of the id column is not between 16 and 17.