SQL BETWEEN Operator | Select Value Within Given Range

SQL BETWEEN is an inbuilt operator in SQL Server. It returns the rows within a given range of a certain column. You can use it with the WHERE Condition in the query. Also, the values can be numbers, text or timestamp. In this article, we will discuss the SQL BETWEEN Operator. Also, we will discuss a few examples of using it.

Note: The BETWEEN Operator in SQL in Inclusive. Therefore, both the minimum and maximum values are included.

Syntax

SELECT column1, column2 … FROM table_name WHERE whereColumn BETWEEN minimumValue AND maximumValue;

• column1, column2 … : The list of columns to select.
• table_name: The name of the table to select from.
• whereColumn: The column to check for where condition.
• minimumValue: The minimum value of the range.
• maximumValue: The maximum value of the range.

The query selects all the rows from the table which have the values of a particular column in the given range of values (inclusive). Also, the above query is similar to the following query:

SELECT column1, column2 … FROM table_name WHERE whereColumn >= minimumValue AND whereColumn <= maximumValue;

Demo Table

Consider the following table for all the queries mentioned in this article.

mysql> SELECT * FROM employees;
+----+--------+--------+------------+-------+---------------------+
| id | name   | salary | experience | team  | joiningDate         |
+----+--------+--------+------------+-------+---------------------+
|  1 | David  |  20000 |          5 | Alpha | 2019-01-01 00:00:00 |
|  2 | Monica |  26000 |          7 | Alpha | 2019-03-01 00:00:00 |
|  3 | John   |  15000 |          3 | Beta  | 2019-06-24 00:00:00 |
|  4 | Mary   |  18000 |          4 | Gamma | 2019-04-10 00:00:00 |
|  5 | Ross   |  30000 |          9 | Beta  | 2019-05-02 00:00:00 |
+----+--------+--------+------------+-------+---------------------+

Examples

Example 1: SQL BETWEEN Integer

For instance, we want to fetch the rows which have experience between 5 and 10.

mysql> SELECT * FROM employees WHERE experience BETWEEN 5 AND 10;
+----+--------+--------+------------+-------+---------------------+
| id | name   | salary | experience | team  | joiningDate         |
+----+--------+--------+------------+-------+---------------------+
|  1 | David  |  20000 |          5 | Alpha | 2019-01-01 00:00:00 |
|  2 | Monica |  26000 |          7 | Alpha | 2019-03-01 00:00:00 |
|  5 | Ross   |  30000 |          9 | Beta  | 2019-05-02 00:00:00 |
+----+--------+--------+------------+-------+---------------------+

The query selects the rows with values between 5 and 10 from the table. Also, the range is inclusive of the minimum and maximum value provided.

Example 2: SQL NOT BETWEEN

Similarly, you can fetch the row do not have values in the range by using the NOT BETWEEN operator. For example, to fetch the rows not having experience between 5 and 10, we can write the following query:

mysql> SELECT * FROM employees WHERE experience NOT BETWEEN 5 AND 10;
+----+------+--------+------------+-------+---------------------+
| id | name | salary | experience | team  | joiningDate         |
+----+------+--------+------------+-------+---------------------+
|  3 | John |  15000 |          3 | Beta  | 2019-06-24 00:00:00 |
|  4 | Mary |  18000 |          4 | Gamma | 2019-04-10 00:00:00 |
+----+------+--------+------------+-------+---------------------+

Example 3: BETWEEN Text Values

Also, you can provide text values in the condition. For instance, the query fetches the rows with the name between David and Mary.

mysql> SELECT * FROM employees WHERE name BETWEEN 'David' AND 'Mary';
+----+-------+--------+------------+-------+---------------------+
| id | name  | salary | experience | team  | joiningDate         |
+----+-------+--------+------------+-------+---------------------+
|  1 | David |  20000 |          5 | Alpha | 2019-01-01 00:00:00 |
|  3 | John  |  15000 |          3 | Beta  | 2019-06-24 00:00:00 |
|  4 | Mary  |  18000 |          4 | Gamma | 2019-04-10 00:00:00 |
+----+-------+--------+------------+-------+---------------------+

Example 4: SQL BETWEEN Datetime

Also, you can select rows according to a date or timestamp range in SQL. The following query fetches rows between the two dates.

mysql> SELECT * FROM employees WHERE joiningDate BETWEEN '2019-03-01' AND '2019-05-01';
+----+--------+--------+------------+-------+---------------------+
| id | name   | salary | experience | team  | joiningDate         |
+----+--------+--------+------------+-------+---------------------+
|  2 | Monica |  26000 |          7 | Alpha | 2019-03-01 00:00:00 |
|  4 | Mary   |  18000 |          4 | Gamma | 2019-04-10 00:00:00 |
+----+--------+--------+------------+-------+---------------------+

Similarly, you can fetch rows between two timestamps using the operator also.

Conclusion

In conclusion, we discussed the SQL BETWEEN Operator in this article. You can read more about it on Wikipedia. Additionally, you can learn about SQL on Concatly.