The BETWEEN and NOT BETWEEN Operator In SQL

BETWEEN Operator
The BETWEEN operator selects a range of data between two values. The values can be numbers, text, or dates.

Syntax

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

Example:

Table (tbl_StuDetails):

S_Id	LastName	FirstName	Age	DOB
1	Verma	Ram	18	02-Apr-1992
2	Sharma	Mohan	23	02-Dec-1987
3	Singh	Hari	20	02-Apr-1990

Now we want to select the Student with age  between 15 and 20 from the table above.


We use the following SELECT statement:

SELECT * FROM tbl_StuDetails WHERE age BETWEEN 15 and 20

The result-set will look like this:

s_Id	LastName	FirstName	Age	DOB
1	Verma	Ram	18	02-Apr-1992
3	Singh	Hari	20	02-Apr-1990

Note: The BETWEEN operator takes initialised values.

---

NOT BETWEEN Operator


To display the Students outside the range in the previous example, use NOT BETWEEN:

SELECT * FROM tbl_StuDetails WHERE age NOT BETWEEN 15 AND 20

The result-set will look like this:

S_Id	LastName	FirstName	Age	DOB
2	Sharma	Mohan	23	02-Dec-1987