SQL BETWEEN Operator

[kumkum]

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.