Using SQL BETWEEN operator

SQL BETWEEN operator is used to get the values for a specified range. (between two values). In between operator both upper and lower range are inclusive.

mysql> SELECT * FROM CUSTOMER;
+----+--------+---------+------+-----------+---------------------+
| ID | FNAME  | LNAME   | AGE  | COUNTRY   | CREATION_DATE       |
+----+--------+---------+------+-----------+---------------------+
|  1 | AKSHAY | KUMAR   |   12 | INDIA     | 2018-05-23 00:16:35 |
|  2 | JONTY  | RHODES  |   40 | CHINA     | 2018-05-18 12:21:12 |
|  3 | JOHNY  | DEPP    |   45 | INDIA     | 2018-05-23 00:16:35 |
|  4 | JESSIE | PINKMAN |   25 | AUSTRALIA | 2018-05-18 12:21:12 |
|  5 | JESSIE | JACKSON |   22 | CANADA    | 2018-05-23 00:16:35 |
|  6 | NICOLE | KIDMAN  |   50 | USA       | 2018-05-18 12:21:12 |
+----+--------+---------+------+-----------+---------------------+

SQL BETWEEN operator syntax and example -

Suppose we want to get the customers with age between 18 to 40, we can use the following statement.

SELECT * FROM CUSTOMER WHERE AGE BETWEEN 18 AND 40;

mysql> SELECT * FROM CUSTOMER;
+----+--------+---------+------+-----------+---------------------+
| ID | FNAME  | LNAME   | AGE  | COUNTRY   | CREATION_DATE       |
+----+--------+---------+------+-----------+---------------------+
|  2 | JONTY  | RHODES  |   40 | CHINA     | 2018-05-18 12:21:12 |
|  4 | JESSIE | PINKMAN |   25 | AUSTRALIA | 2018-05-18 12:21:12 |
|  5 | JESSIE | JACKSON |   22 | CANADA    | 2018-05-23 00:16:35 |
+----+--------+---------+------+-----------+---------------------+
3 rows in set (0.00 sec)

Here we can see that we have got customers with age between 18 and 40. (inclusive 18 and 40 as mentioned earlier).

SQL BETWEEN operator syntax and example for dates -

To get customers with creation date between 18 and 23 May (inclusive these dates).