SQL BETWEEN Operator
SQL BETWEEN operator is used to allows values with WHERE clause for providing a range of values.
- The values can be numbers, text, dates.
- BETWEEN Operator is inclusive: start and end values are included.
BETWEEN Operator Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value_1 AND value_2;
EXAMPLE: BETWEEN Operator
Student Table
A table of student records, the name of table is "Student", and the name of columns are [ROLL_NO, STUDENT_NAME, ADDRESS, MOBILE_NO, AGE, and BRANCH]:
| ROLL_NO |
STUDENT_NAME |
ADDRESS |
MOBILE_NO |
AGE |
BRANCH |
| 1 |
Mohit Sharma |
Delhi |
XXXXXXXX87 |
20 |
IT |
| 2 |
Pooja Pandit |
Kolkata |
XXXXXXXX34 |
18 |
CSE |
| 3 |
Priya Pandey |
Delhi |
XXXXXXXX62 |
19 |
EEE |
| 4 |
Mohit Kumar Jha |
Allahabad U.P |
XXXXXXXX87 |
20 |
ECE |
| 5 |
Shevani Shree |
Noida U.P |
XXXXXXXX51 |
17 |
IT |
| 6 |
Badshah |
Mumbai |
XXXXXXXX02 |
20 |
Civil |
| 7 |
Viki Mohan |
Pune |
XXXXXXXX72 |
19 |
CSE |
| 8 |
Sona Kaur |
Indor M.P |
XXXXXXXX81 |
22 |
ME |
BETWEEN Operator
SELECT *
FROM Student
WHERE AGE BETWEEN 17 AND 19;
Result Look Like
| ROLL_NO |
STUDENT_NAME |
ADDRESS |
MOBILE_NO |
AGE |
BRANCH |
| 2 |
Pooja Pandit |
Kolkata |
XXXXXXXX34 |
18 |
CSE |
| 3 |
Priya Pandey |
Delhi |
XXXXXXXX62 |
19 |
EEE |
| 5 |
Shevani Shree |
Noida U.P |
XXXXXXXX51 |
17 |
IT |
| 7 |
Viki Mohan |
Pune |
XXXXXXXX72 |
19 |
CSE |
NOT BETWEEN Operator
SELECT *
FROM Student
WHERE AGE NOT BETWEEN 17 AND 19;
Result Look Like
| ROLL_NO |
STUDENT_NAME |
ADDRESS |
MOBILE_NO |
AGE |
BRANCH |
| 1 |
Mohit Sharma |
Delhi |
XXXXXXXX87 |
20 |
IT |
| 4 |
Mohit Kumar Jha |
Allahabad U.P |
XXXXXXXX87 |
20 |
ECE |
| 6 |
Badshah |
Mumbai |
XXXXXXXX02 |
20 |
Civil |
| 8 |
Sona Kaur |
Indor M.P |
XXXXXXXX81 |
22 |
ME |
EXAMPLE: BETWEEN Date
Orders Table
This is a sample ORDER table; The columns of the table are [OID, DATE, CUSTOMER_ID, and AMOUNT]:
| OID |
DATE |
CUSTOMER_ID |
AMOUNT |
| 210 |
12/01/2019 |
2 |
4000 |
| 209 |
11/22/2019 |
4 |
2000 |
| 211 |
12/10/2019 |
1 |
1000 |
| 212 |
12/17/2019 |
6 |
3000 |
BETWEEN Dates Example
SELECT *
FROM ORDERS
WHERE DATE BETWEEN #12/01/2019# AND #12/17/2019#;
Result Look Like
| OID |
DATE |
CUSTOMER_ID |
AMOUNT |
| 210 |
12/01/2019 |
2 |
4000 |
| 211 |
12/10/2019 |
1 |
1000 |
| 212 |
12/17/2019 |
6 |
3000 |