SQL BETWEEN Operator

Submitted by admin on Monday, March 14, 2011 - 09:35.

The SQL BETWEEN operator is used with the WHERE clause in the SELECT statement, this can be used to look for the data between the minimum and the maximum values given to the clause, The BETWEEN clause has DBMS specific behavior, some of the DBMS will do an inclusive between and some will do exclusive. That will also include the values mentioned as the minimum and maximum and some will ignore the minimum and maximum and will simply list the values in between them.

The BETWEEN clause can be used in numeric as well as alphabetical data, numeric is simple and for alphabetical data it looks the data alphabetically.

SQL BETWEEN Syntax

SELECT column_name(s) FROM TABLE_NAME WHERE column_name BETWEEN "min value" AND "max value"

Consider the following two tables for this exercise

Users

Firstname	Lastname	Salary	deptnumber
John	Smith	1000	1
Mathew	Simon	3000	1
Bill	Steve	2200	1
Amanda	Rogers	1800	2
Steve	Hills	2800	2
Steve	jobs	2400	2
bill	cosby	700	3

Example # 1

SELECT Firstname,Lastname,salary FROM users WHERE salary BETWEEN "1500" AND "2500"

Result of the Query

Firstname	Lastname	Salary
Bill	Steve	2200
Amanda	Rogers	1800
Steve	jobs	2400

This Result of the query includes all the data set which has the salary in between the min and max value (i.e 1500 and 2500 dollars).

Example # 2

SELECT Firstname,Lastname,salary FROM Users WHERE Firstname BETWEEN "John" AND "Steve"

Result of the Query

Firstname	Lastname	Salary	deptnumber
Mathew	Simon	3000	1

This Result of the query is containing the data set which has values in between the min "john" and Max "steve " based on alphabetical order.

SQL BETWEEN Operator

Example # 1

Example # 2

Add new comment