SQL BETWEEN OPERATOR

The BETWEEN operator is used fetch records within range of different values. The values can be Number, Date or Texts. You have to define here start and end values while writing sql statement. To define start and end values, you may use AND operator in your sql statement.

You may also use Between operator with IN and NOT operators.

Let’s understand each with syntax and examples.

My SQL

Syntax:

SELECT COLUMN1, COLUMN2, COLUMN3, ...
FROM TABLE_NAME
WHERE COLUMN1 BETWEEN VALUE1 AND VALUE2;

Example:

Original Data in table called “Employee”

Id Employee Code First Name Last name Gender Birth Date Expense
1 EMP_1001 John Peter Male 10-11-1982 200
2 EMP_1002 Josh Wills Male 09-07-1980 250
3 EMP_1003 Jeremy Parkar Female 08-09-1990 150
4 EMP_1004 Aidan Walls Male 05-03-1989 300
5 EMP_1005 Adam Mondry Male 02-06-1991 100


SELECT * FROM Employee
WHERE Expense Between 150 and 250

Data displayed from table “Employee” after above Sql Query executed

Id Employee Code First Name Last name Gender Birth Date Expense
1 EMP_1001 John Peter Male 10-11-1982 200
2 EMP_1002 Josh Wills Male 09-07-1980 250
3 EMP_1003 Jeremy Parkar Female 08-09-1990 150

So, in above result set, we can see only three records who has expense range 150 to 250 only. Rest of records does not display.

In similar way, if we can use NOT with BETWEEN, we can see the following sql and result set.


SELECT * FROM Employee
WHERE Expense NOT Between 150 and 250

Data displayed from table “Employee” after above Sql Query executed

Id Employee Code First Name Last name Gender Birth Date Expense
4 EMP_1004 Aidan Walls Male 05-03-1989 300
5 EMP_1005 Adam Mondry Male 02-06-1991 100

In above example, we can see two records whose range of expense are below 100 and above 300.

Another example for BETWEEN operator with date range


SELECT * FROM Employee
WHERE Birthdate Between '01/01/1982' and '12/31/1990'

Id Employee Code First Name Last name Gender Birth Date Expense
1 EMP_1001 John Peter Male 10-11-1982 200
3 EMP_1003 Jeremy Parkar Female 08-09-1990 150
4 EMP_1004 Aidan Walls Male 05-03-1989 300