WEB DEVELOPER SITE
HTMLCSSJAVASCRIPTSQLPHPBOOTSTRAPJQUERYANGULARXML
 

SQL BETWEEN Operator


The BETWEEN operator is used to select values within a data range between two values.


SQL BETWEEN operator

The BETWEEN operator picks a value within a data range between two values. These values can be numeric, text, or dates.

SQL BETWEEN syntax

SELECT column_name (s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;


Demo database

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

Here is the data from the "Websites" table:

+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/ | 1     | USA     |
| 2  | yahoo          | https://www.yahoo.com/| 13    | IN                  |
| 3  | Indiatimes      | http://www.Indiatimes.com/ | 4689  | IN                  |
| 4  | zomato          | http://zomato.com/      | 20    | IN                  |
| 5  | Facebook     | https://www.facebook.com/| 3     | USA     |
| 7  | stackoverflow | http://stackoverflow.com/|   0 | IND     |
+----+---------------+---------------------------+-------+---------+


Example of BETWEEN operator

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

Example

SELECT * FROM Websites
WHERE alexa BETWEEN 1 AND 20;

:



NOT BETWEEN operator example

To display websites that are not in the examples above, use NOT BETWEEN:

Example

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

:



Example of BETWEEN operator with IN

The following SQL statement selects all websites with alexa between 1 and 20, but country other than USA and IND:

Example

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

:



Example of BETWEEN operator with text value

The following SQL statement selects all sites whose name starts with a letter between 'A' and 'H':

Example

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

:



Example of NOT BETWEEN operator with text value

The following SQL statement selects all sites whose name does not begin with a letter between 'A' and 'H':

Example

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

:



Example table

Here is the data for the "access_log" website visit log, where:

  • aid: is the incrementing id.
  • site_id : The website id corresponding to the websites table.
  • count : The number of visits.
  • date: is the date of the visit.
  • 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 access_log table SQL file used in this tutorial: access_log.sql .



    Example of BETWEEN operator with date value

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

    Example

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

    :


    Please note that the BETWEEN operator produces different results in different databases!
    In some databases, BETWEEN picks fields that are between two values but exclude two test values.
    In some databases, BETWEEN picks a field that is between two values and includes two test values.
    In some databases, BETWEEN picks fields that are between two values and include the first test value but not the last test value.

    So, check how your database handles the BETWEEN operator!