Next Chapter »

SQL Tutorial - Between operator


The 'BETWEEN' operator is used to test to see whether or not a column stated before the keyword BETWEEN contains a value which is present in "between" the two values stated in the condition inclusive of them.

The Between operator


The 'BETWEEN ' clause helps you to search for one or more records from a table based on a specified range of values given in the condition.

SQL 'BETWEEN' operator Syntax


Select column-3,column-1,column-2,... column-n
from table
where column3 Between value1 and value2

The Between operator Example


The 'Employees' tables contains the following columns and their respective data type as shown below.

EmployeeID EmployeeName DateOfBirth DesignationID DeptID City Salary
1 Richard Hughes 4/23/1945 12:00:00 AM 1 1 New Orleans 950.00
2 Taryn Sinclair 3/22/1980 12:00:00 AM 2 2 San Francisco 800.00
3 Ted Horowitz 3/31/1960 12:00:00 AM 3 4 New York 700.00
4 Jonathan Douglas 7/7/1971 12:00:00 AM 3 4 Salt Lake City 600.00
5 Miranda Leigh 7/19/1983 12:00:00 AM 7 3 New York 500.00
6 Jana Rae 9/23/1976 12:00:00 AM 8 2 Houston 400.00
7 Lita Rosanna 9/14/1982 12:00:00 AM 4 6 Long Island 650.00
8 Colin Flooks 12/29/1988 12:00:00 AM 4 2 Salt Lake City 600.00
9 Anthony Frank 2/22/1988 12:00:00 AM 9 3 San Francisco 750.00
10 Stephanie Lynn 7/30/1979 12:00:00 AM 8 2 Salt Lake City 450.00
11 Jack Martin 8/25/1985 12:00:00 AM NULL NULL Austin 500.00

Select EmployeeID,EmployeeName,DateOfBirth,city, Salary
from Employees
where DateOfBirth between '01/01/1970' and '12/31/1980'

The 'BETWEEN' operator retrieves all the neccessary records from the 'Employees' table for employees whose date of birth lie between '01/01/1970' and '12/31/1980' shown below.

EmployeeID EmployeeName DateOfBirth city Salary
2 Taryn Sinclair 3/22/1980 12:00:00 AM San Francisco 800.00
4 Jonathan Douglas 7/7/1971 12:00:00 AM Salt Lake City 600.00
6 Jana Rae 9/23/1976 12:00:00 AM Houston 400.00
10 Stephanie Lynn 7/30/1979 12:00:00 AM Salt Lake City 450.00

Note : You can also use NOT BETWEEN operator in the same manner to exclude the values between your range.

SQL 'Not BETWEEN' operator Syntax


Select column-3,column-1,column-2,... column-n
from table
where column3 NOT Between value1 and value2


Next Chapter »