The BETWEEN operator in SQL is used to filter results within a specific range.
It is used in the WHERE clause of a SELECT, UPDATE, or DELETE statement to filter rows based on a specific range of values.
The BETWEEN operator is inclusive, meaning that it will include the values specified in the range.
Syntax
The syntax for using the BETWEEN operator is as follows:
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;In this syntax, you specify the column(s) you want to select, the table you want to select from, and then use the WHERE clause to filter the results based on a specific range of values.
The column_name should be replaced with the name of the column you want to filter on, value1 is the start of the range and value2 is the end of the range.
Examples
To illustrate how the BETWEEN operator works, let's take a look at a few examples using the following table:
+----+--------+
| ID | Name |
+----+--------+
| 1 | John |
| 2 | Michael|
| 3 | Brad |
| 4 | Tom |
| 5 | Jerry |
+----+--------+Example 1:
To get all the rows where the ID is between 2 and 4, the following SQL statement can be used:
SELECT * FROM table_name
WHERE ID BETWEEN 2 AND 4;The result will be:
+----+---------+
| ID | Name |
+----+---------+
| 2 | Michael |
| 3 | Brad |
| 4 | Tom |
+----+---------+Example 2:
To get all the rows where the Name is between "Brad" and "Tom", the following SQL statement can be used:
SELECT * FROM table_name
WHERE Name BETWEEN "Brad" AND "Tom";The result will be:
+----+-------+
| ID | Name |
+----+-------+
| 3 | Brad |
| 4 | Tom |
+----+-------+Note that the BETWEEN operator is case-sensitive, so if you want to make it case-insensitive, you can use the UPPER or LOWER function to make sure that the values are in the same case before using the BETWEEN operator.
Example 3:
To make the BETWEEN operator case-insensitive we can use the UPPER function as follows:
SELECT * FROM table_name
WHERE UPPER(Name) BETWEEN "BRAD" AND "TOM";The result will be same as example 2.
Combining with other operators
The BETWEEN operator can also be used in combination with other operators.
For example, you can use the BETWEEN operator with the AND or OR operator to filter results based on multiple conditions.
Example 4:
To get all the rows where the ID is between 2 and 4 and the Name is "Brad", the following SQL statement can be used:
SELECT * FROM table_name
WHERE ID BETWEEN 2 AND 4
AND Name = "Brad";The result will be:
+----+-------+
| ID | Name |
+----+-------+
| 3 | Brad |
+----+-------+Example 5:
To get all the rows where the ID is between 2 and 4 or the Name is "Brad", the following SQL statement can be used:
SELECT * FROM table_name
WHERE ID BETWEEN 2 AND 4
OR Name = "Brad";The result will be:
+------+--------+
| ID | Name |
+------+--------+
| 2 | Michael|
| 3 | Brad |
| 4 | Tom |
+------+--------+In Example 4, only the rows where the ID is between 2 and 4 and the Name is "Brad" are returned.
In Example 5, all the rows where the ID is between 2 and 4 or the Name is "Brad" are returned.
In conclusion, the BETWEEN operator in SQL is a useful tool for filtering results within a specific range.
It can be used in the WHERE clause of a SELECT, UPDATE, or DELETE statement and can also be combined with other operators to filter results based on multiple conditions.
It is important to keep in mind that the BETWEEN operator is inclusive, case-sensitive and can be made case-insensitive by using the UPPER or LOWER function.