In This Article, You Will Know About SQL BETWEEN Operator.
SQL IN Operator – Before moving ahead, let’s know a bit about SQL IN Operator.
Table of Contents
BETWEEN Operator
BETWEEN Operator – The SQL BETWEEN operator is used to select records within a range. It includes both beginning and end values. A value can be a number, text, etc.
Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
Example Database
Below is the example of a database table named “details.”
Id | Name | Class | section_id |
1 | Alex | 5 | 1 |
2 | Rolex | 6 | 2 |
3 | Salex | 7 | 2 |
4 | Lelex | 8 | 3 |
5 | Kelex | 9 | 5 |
BETWEEN Number Example
Example: Use BETWEEN Operator to return the record with a range.
SELECT Class
FROM details
WHERE Class BETWEEN 5 AND 8;
As a result, it returns the Class from 5 to 8.
Example: Use BETWEEN Operator to return the record with a range.
SELECT * FROM details
WHERE Class BETWEEN 5 AND 8;
As a result, it returns the Class from 5 to 8.
Example: Use BETWEEN Operator to return the record with a range.
SELECT Class
FROM details
WHERE Class NOT BETWEEN 6 AND 8;
As a result, it returns the Class from 6 to 8.
Example: Use BETWEEN Operator to return the record with a range.
SELECT Class
FROM details
WHERE Class BETWEEN 5 AND 8
AND section_id NOT IN (1,3,5);
As a result, it returns the Class from 5 to 8.
BETWEEN Text Value Example
Example: Use BETWEEN Operator to return the record with a range.
SELECT * FROM details
WHERE Name BETWEEN 'Alex' AND 'Lelex';
As a result, it returns names within the range.
Example: Use BETWEEN Operator to return the record with a range.
SELECT * FROM details
WHERE Name BETWEEN 'Alex' AND 'Lelex'
ORDER BY section_id;
As a result, it returns names within the range ordered by section_id.
Example: Use BETWEEN Operator to return the record with a range.
SELECT * FROM details
WHERE Name NOT BETWEEN 'Rolex' AND 'Lelex';
As a result, it returns names within the range.
Sample Data
Id | Name | Class | section_id | admission_date |
1 | Alex | 5 | 1 | 10-02-2010 |
2 | Rolex | 6 | 2 | 11-03-2010 |
3 | Salex | 7 | 2 | 12-04-2010 |
4 | Lelex | 8 | 3 | 13-05-2010 |
5 | Kelex | 9 | 5 | 14-06-2010 |
BETWEEN Date Example
Example: Use BETWEEN Operator to return the record with a range.
SELECT * FROM details
WHERE admission_date BETWEEN #10-02-2010# AND #13-05-2010#;
As a result, it returns the admission dates within the range.
Example: Use BETWEEN Operator to return the record with a range.
SELECT * FROM details
WHERE admission_date NOT BETWEEN #11-03-2010# AND #13-05-2010#;
As a result, it returns the admission dates within the range.
If you find anything incorrect in the above-discussed topic and have further questions, please comment below.
Connect on:
