SQL BETWEEN Operator

The BETWEEN operator is used to select values within a range of two values. These values can be numbers, text, or dates.

SQL BETWEEN Operator

The BETWEEN operator selects values within a range of two values. These values can be numbers, text, or dates.

SQL BETWEEN Syntax

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

Parameter Explanation:

• column1, column2, ...: The names of the fields to be selected. Multiple fields can be specified. If no field names are specified, all fields will be selected.

• table_name: The name of the table to be queried.

• column: The name of the field to be queried.

• value1: The starting value of the range.

• value2: The ending value of the range.

Demo Database

In this tutorial, we will use the tutorialpro sample database.

Below is data from the "Websites" table:

mysql> SELECT * FROM Websites;
+----+---------------+---------------------------+-------+---------+
| id | name          | url                       | alexa | country |
+----+---------------+---------------------------+-------+---------+
|  1 | Google        | https://www.google.cm/    |     1 | USA     |
|  2 | 淘宝          | https://www.taobao.com/   |    13 | CN      |
|  3 | tutorialpro.org       | http://www.tutorialpro.org/    |  5000 | USA     |
|  4 | 微博           | 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 with an alexa ranking between 1 and 20:

Example

SELECT * FROM Websites WHERE alexa BETWEEN 1 AND 20;

Execution Output:

NOT BETWEEN Operator Example

To display websites that are not within 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 with an alexa ranking between 1 and 20 but with a country not in 'USA' and '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 the name starts with a letter 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 the name does not start with a letter between 'A' and 'H':

Example

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

Execution Output:

Example Table

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

• aid: Auto-incremented id.

• site_id: Corresponds to the website id in the websites table.

• count: Number of visits. date: Represents the access 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.

Example of BETWEEN Operator with Date Values

The following SQL statement selects all access records where the 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:

| | Please note that BETWEEN operator may yield different results in different databases! 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 exclude the last test value. Therefore, check how your database handles the BETWEEN operator! | | --- | --- |