SQL BETWEEN Operator
SQL BETWEEN operator used for fetching within range data. SQL BETWEEN query simply a shorthand way of expressing an inclusive range comparison.
SQL Between operator support only range type value like number, dates, character. But not supporting boolean, string value range.
Syntax
Consider following syntax that help you to understanding BETWEEN clause,
SELECT * FROM table_name
WHERE column_name
BETWEEN lower_bound_value AND upper_bound_value;
When SQL query with BETWEEN clause parse into SQL Buffer, it will automatically expand out into separate comparison clauses.
SELECT * FROM table_name
WHERE column_name <= lower_bound_value
AND column_name >= upper_bound_value;
Example
In this example select all users_info table rows with range between no column value 1 to 5.
SQL> SELECT * FROM users_info WHERE no BETWEEN 1 AND 5;
NO NAME ADDRESS CONTACT_NO BIRTH_DATE
--- --------------------- ------------------------- --------------- -------------
1 Opal Kole 63 street Ct. 000-444-7847 1984-05-07
2 Max Miller 41 NEW ROAD. 000-444-8292 1987-07-07
3 Beccaa Moss 2500 green city. 000-444-7586 1992-01-15
4 Paul Singh 1343 Prospect St 000-444-7585 1998-07-25
5 Ken Myer 137 Clay Road 000-444-7528 2002-04-07
SQL BETWEEN clause with DATE
In this example select all users_info table rows with range between birth_date column value '1990-01-01' to '2000-12-31'.
SQL> SELECT * FROM users_info WHERE birth_date BETWEEN '1990-01-01' AND '2000-12-31';
NO NAME ADDRESS CONTACT_NO BIRTH_DATE
--- --------------------- ------------------------- --------------- -------------
3 Beccaa Moss 2500 green city. 000-444-7586 1992-01-15
4 Paul Singh 1343 Prospect St 000-444-7585 1998-07-25
6 Jack Evans 1365 Grove Way 000-444-8401 1993-04-24
8 Gabe Hee 1220 Dallas Drive 000-444-5028 1992-08-27
9 Ben Mares 101 Candy Road 000-444-5928 1999-11-24
5 rows selected.
SQL BETWEEN clause with NOT IN
In this example select all users_info table rows with range NOT IN between no column value 1 to 5. Use only NOT keyword instead of NOT IN.
SQL> SELECT * FROM users_info WHERE no NOT BETWEEN 6 AND 10;
NO NAME ADDRESS CONTACT_NO BIRTH_DATE
--- --------------------- ------------------------- --------------- -------------
1 Opal Kole 63 street Ct. 000-444-7847 1984-05-07
2 Max Miller 41 NEW ROAD. 000-444-8292 1987-07-07
3 Beccaa Moss 2500 green city. 000-444-7586 1992-01-15
4 Paul Singh 1343 Prospect St 000-444-7585 1998-07-25
5 Ken Myer 137 Clay Road 000-444-7528 2002-04-07
5 rows selected.