1. Home
  2. SQL BETWEEN operator

The best SQL BETWEEN operator Tutorial In 2024, In this tutorial you can learn SQL BETWEEN operator,The demo database,BETWEEN operator examples,Examples,NOT BETWEEN operator examples,Examples,IN BETWEEN operator with examples,Examples,BETWEEN operator with text values ​​Examples,Examples,NOT BETWEEN operator with text values ​​Examples,Examples,Example Table,BETWEEN operators with date values ​​Examples,Examples,

SQL BETWEEN operator

BETWEEN operator is used to select a value within the range of data between two values ​​of.

SQL BETWEEN operator

BETWEEN operator to select a value within the range of data between two values ​​of. These values ​​can be numeric, text or date.

SQL BETWEEN syntax

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

The demo database

In this tutorial, we will use w3big sample database.

The following is a selected "Websites" table data: 

+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/    | 1     | USA     |
| 2  | 淘宝          | https://www.taobao.com/   | 13    | CN      |
| 3  | 本教程      | http://www.w3write.com/    | 4689  | CN      |
| 4  | 微博          | http://weibo.com/         | 20    | CN      |
| 5  | Facebook     | https://www.facebook.com/ | 3     | USA     |
| 7  | stackoverflow | http://stackoverflow.com/ |   0 | IND     |
+----+---------------+---------------------------+-------+---------+

BETWEEN operator examples

The following SQL statement select alexa between 1 and 20, all of the sites:

Examples

SELECT * FROM Websites
WHERE alexa BETWEEN 1 AND 20;

Execution output:


NOT BETWEEN operator examples

To display the site is not within the scope of the examples above, use NOT BETWEEN:

Examples

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

Execution output:


IN BETWEEN operator with examples

The following SQL statement select alexa between 1 and 20, but the country is not USA and IND for all Web sites:

Examples

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

Execution output:


BETWEEN operator with text values ​​Examples

The following SQL statement to select all the site name with the letter between 'A' and 'H' between the beginning:

Examples

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

Execution output:


NOT BETWEEN operator with text values ​​Examples

The following SQL statement is not between all the sites chosen name beginning with the letter between 'A' and 'H':

Examples

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

Execution output:


Example Table

Here is the data "access_log" website access record table, wherein:

  • aid: the increment id.
  • site_id: table corresponding websites website id.
  • count: the number of visits.
  • date: the date for 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)

    This tutorial uses the SQL file to access_log table: access_log.sql .


    BETWEEN operators with date values ​​Examples

    The following SQL statement to select all access to records date between '2016-05-10' and '2016-05-14' between:

    Examples

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

    Execution output:


    lamp

    Note that in different databases, BETWEEN operator will produce different results!
     In some databases, BETWEEN select between two values ​​but does not include two field test values.
    In some databases, BETWEEN choose between and including two field test values ​​between the two values.
    In some databases, BETWEEN select between two values ​​including the first test value and excluding the last test value field.

    Therefore, check your database is how to handle BETWEEN operator!

    Hot Tools

    Other Tools

    Online Tutorial

    Website Navigation

    SQL BETWEEN operator
    10/30