SQL Between Operator in SQL
Table of Content:
The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.
The BETWEEN operator is inclusive: begin and end values are included.
Syntax:
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;
Prerequisite Codes
If you don't have table please create below table and practice it.
My database name is SQLExamples
USE SQLExamples
Create this below Employee table
DROP TABLE Employee CREATE TABLE Employee( EmpId INT, EmpName VARCHAR(25), EmpAddress VARCHAR(100), EmpDept VARCHAR(25) )
Insert data inside table
INSERT INTO Employee VALUES (1, 'Rambo', 'Kolkata', 'IT'), (2, 'Rohit', 'Kolkata', 'IT'), (3, 'Rohon', 'Kolkata', 'ITIS'), (4, 'Ronok', 'Kolkata', 'ITIS'), (5, 'Rubin', 'Kolkata', 'ITIS'), (6, 'Sorif', 'Kolkata', 'ADMIN'), (7, 'Soriful', 'Kolkata', 'ADMIN'), (8, 'Sofik', 'Kolkata', 'ADMIN')
SELECT * FROM Employee
Output:
The above code will produce the following result-
EmpId |
EmpName |
EmpAddress |
EmpDept |
1 |
Rambo |
Kolkata |
IT |
2 |
Rohit |
Kolkata |
IT |
3 |
Rohon |
Kolkata |
ITIS |
4 |
Ronok |
Kolkata |
ITIS |
5 |
Rubin |
Kolkata |
ITIS |
6 |
Sorif |
Kolkata |
ADMIN |
7 |
Soriful |
Kolkata |
ADMIN |
8 |
Sofik |
Kolkata |
ADMIN |
Example: BETWEEN
Code:
SELECT * FROM Employee WHERE EmpId BETWEEN 2 AND 4
Output:
The above code will produce the following result-
EmpId |
EmpName |
EmpAddress |
EmpDept |
2 |
Rohit |
Kolkata |
IT |
3 |
Rohon |
Kolkata |
ITIS |
4 |
Ronok |
Kolkata |
ITIS |
Example: NOT BETWEEN
Code:
SELECT * FROM Employee WHERE EmpId NOT BETWEEN 2 AND 4
Output:
The above code will produce the following result-
EmpId |
EmpName |
EmpAddress |
EmpDept |
1 |
Rambo |
Kolkata |
IT |
5 |
Rubin |
Kolkata |
ITIS |
6 |
Sorif |
Kolkata |
ADMIN |
7 |
Soriful |
Kolkata |
ADMIN |
8 |
Sofik |
Kolkata |
ADMIN |
Example: BETWEEN with IN
Code:
SELECT * FROM Employee WHERE EmpId BETWEEN 2 AND 7 AND EmpDept NOT IN ('IT', 'ITIS')
Output:
The above code will produce the following result-
EmpId |
EmpName |
EmpAddress |
EmpDept |
6 |
Sorif |
Kolkata |
ADMIN |
7 |
Soriful |
Kolkata |
ADMIN |