Prev Next
BETWEEN…AND operator in SQL are used to select in-between values from the given range/ values. It is used in a WHERE clause in SELECT, UPDATE and DELETE statements/queries. Syntax for SQL BETWEEN…AND operators are given below.
Syntax for BETWEEN…AND operator in SQL:
| Syntax for SQL BETWEEN…AND operator | SELECT column_name1, column_name2, etc FROM table_name WHERE column_name1 BETWEEN value1 AND value2; |
| Syntax for SQL NOT BETWEEN…AND operator | SELECT column_name1, column_name2, etc FROM table_name WHERE column_name1 NOT BETWEEN value1 AND value2; |
Please consider the following table with few records as given below.
Table name (for example): student
Column names in this table: Student_ID, Student_name, City and Age
Available records: 4 rows
Example1: How to use BETWEEN…AND in a WHERE clause in SELECT queries
SQL query:
SELECT Student_ID, Student_name, City, Age from student
WHERE Age BETWEEN 19 AND 25;
SQL query Output:
Example2: How to use NOT BETWEEN…AND in WHERE clause in SELECT queries
SQL query:
SELECT Student_ID, Student_name, City, Age from student
WHERE Age NOT BETWEEN 19 AND 25;
SQL query Output:
Note:
- Sometime, BETWEEN operators might not consider fetching the starting and ending values in the given range when we select. This behaves based on the databases we use like Oracle, MySQL etc. To check how it behaves, please test this query in different databases before you use.