Skip to main content

SQL BETWEEN Operator

Body

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.

Add new comment