SQL BETWEEN Operator

BETWEEN operator selects values within a range of values, which can be numeric, text, or date.

SQL BETWEEN Syntax

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

Parameter Description:

• column1, column2, ...: The names of the fields to select. Multiple fields can be specified. If no field name is specified, all fields will be selected.
• table_name: The name of the table to query.
• column: The name of the field to query.
• value1: The starting value of the range.
• value2: The ending value of the range.

---

Demo Database

In this tutorial, we will use the sample database.

Here is the data from the "Websites" table:

mysql> SELECT * FROM Websites;
+----+---------------+---------------------------+-------+---------+
| id | name          | url                       | alexa | country |
+----+---------------+---------------------------+-------+---------+
| 1  | Google        | https://www.google.cm/    | 1     | USA     |
| 2  | Taobao        | https://www.taobao.com/   | 13    | CN      |
| 3  |         |     | 5000  | USA     |
| 4  | Weibo         | http://weibo.com/         | 20    | CN      |
| 5  | Facebook      | https://www.facebook.com/ | 3     | USA     |
| 7  | stackoverflow | http://stackoverflow.com/ | 0     | IND     |
+----+---------------+---------------------------+-------+---------+

---

BETWEEN Operator Example

The following SQL statement selects all websites where alexa is between 1 and 20:

Example

SELECT * FROM Websites
WHERE alexa BETWEEN 1 AND 20;

Execution Output:

---

NOT BETWEEN Operator Example

To display websites not in the range of the above example, use NOT BETWEEN:

Example

SELECT * FROM Websites
WHERE alexa NOT BETWEEN 1 AND 20;

Execution Output:

---

BETWEEN Operator with IN Example

The following SQL statement selects all websites where alexa is between 1 and 20 but country is not USA or IND:

Example

SELECT * FROM Websites
WHERE (alexa BETWEEN 1 AND 20)
AND country NOT IN ('USA', 'IND');

Execution Output:

---

BETWEEN Operator with Text Values Example

The following SQL statement selects all websites where name starts with letters between 'A' and 'H':

Example

SELECT * FROM Websites
WHERE name BETWEEN 'A' AND 'H';

Execution Output:

---

NOT BETWEEN Operator with Text Values Example

The following SQL statement selects all websites where name does not start with letters between 'A' and 'H':

Example

SELECT * FROM Websites
WHERE name NOT BETWEEN 'A' AND 'H';

Execution Output:

---

Sample Table

Here is the data from the "access_log" website access log table, where:

• aid: is an auto-incrementing id.
• site_id: is the website id corresponding to the websites table.
• count: is the number of visits.
• date: is the visit date.

mysql> SELECT * FROM access_log;
+-----+---------+-------+------------+
| aid | site_id | count | date       |
+-----+---------+-------+------------+
| 1   | 1       | 45    | 2016-05-10 |
| 2   | 3       | 100   | 2016-05-13 |
| 3   | 1       | 230   | 2016-05-14 |
| 4   | 2       | 10    | 2016-05-14 |
| 5   | 5       | 205   | 2016-05-14 |
| 6   | 4       | 13    | 2016-05-15 |
| 7   | 3       | 220   | 2016-05-15 |
| 8   | 5       | 545   | 2016-05-16 |
| 9   | 3       | 201   | 2016-05-17 |
+-----+---------+-------+------------+
9 rows in set (0.00 sec)

The SQL file for the access_log table used in this tutorial: access_log.sql.

---

BETWEEN Operator with Date Values Example

The following SQL statement selects all access records where date is between '2016-05-10' and '2016-05-14':

Example

SELECT * FROM access_log
 WHERE date BETWEEN '2016-05-10' AND '2016-05-14';

Execution Output:

Note: In different databases, the BETWEEN operator may produce different results! In some databases, BETWEEN selects fields that are between two values but do not include the two test values. In some databases, BETWEEN selects fields that are between two values and include both test values. In some databases, BETWEEN selects fields that are between two values and include the first test value but not the last test value. Therefore, please check how your database handles the BETWEEN operator!