SQL BETWEEN Operator


The SQL BETWEEN operator is used to fetch or extract values within a given range.

The SQL BETWEEN keyword is a conditional based operator, that meet a condition falling between a specified range of given values.

It can work with any type of data like numbers, text, or dates.


SQL BETWEEN Syntax

The below syntax is used to define between operator in SQL WHERE clause:


SELECT column_name1, column_name2 FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Note: value1 and value2 must be the same datatype and It cannot be different.


Sample Database Table - Books

BookId BookName BookPrice RelYear DomainName AuthorName
1 Sql Server Concurrency 136.33 2006 Security Padmavathi
2 Troubleshooting SQL Server 70 2009 Optimization Keshavan
3 The SQL Programming Language 84.22 2008 Optimization Dharan
4 Sql Server Interview Questions 90 2015 Administration Varshini Kutty
5 Introduction to SQL and PL/SQL 195 2007 Administration Ramanathan
6 The Practical SQL Handbook 180 2014 Database Geetha
7 Pro SQL Azure 123.45 2007 Optimization Pandurengan
8 Foundations Of Sql Server 2008 60 2012 Optimization Sakunthala
9 Oracle Concurrency 205 2007 Security Pandurengan
10 Practical SQL 160 2011 Administration Hari Krishnan
11 Oracle All-in-One For Dummies 199.97 2015 Programming Chandra
12 Making Sense Of SQL 80 2009 Database Hari Krishnan

Search Keys

  • sql between dates
  • sql server between
  • sql date between
  • query between
  • tsql between
  • between clause in sql
  • how to use between in sql
  • sql server between date
  • not between sql
  • sql between inclusive
  • between sql date
  • sql between example
  • where between sql
  • sql query between
  • between sql command
  • sql server date between query example
  • sql operator between
  • oracle between operator
  • sql search between dates

SQL BETWEEN Operator Example With Numbers

The following SQL SELECT statement selects the all books with a BookPrice Between "100" AND "200", in the "Books" table:


SELECT * FROM Books 
WHERE 
BookPrice BETWEEN 100 AND 200;

The result of above query is:

BookId BookName BookPrice RelYear DomainName AuthorName
1 Sql Server Concurrency 136.33 2006 Security Padmavathi
5 Introduction to SQL and PL/SQL 195 2007 Administration Ramanathan
6 The Practical SQL Handbook 180 2014 Database Geetha
7 Pro SQL Azure 123.45 2007 Optimization Pandurengan
10 Practical SQL 160 2011 Administration Hari Krishnan
11 Oracle All-in-One For Dummies 199.97 2015 Programming Chandra

It checks for numeric value:

  • Value1:- Greater than or equal
  • Value2:- Less than or equal

SQL NOT BETWEEN Operator Example With Numbers

The SQL BETWEEN condition can use with the SQL NOT operator.

To display the books outside the range of the previous example, use NOT BETWEEN:


SELECT * FROM Books 
WHERE 
BookPrice NOT BETWEEN 100 AND 200;

The result of above query is:

BookId BookName BookPrice RelYear DomainName AuthorName
2 Troubleshooting SQL Server 70 2009 Optimization Keshavan
3 The SQL Programming Language 84.22 2008 Optimization Dharan
4 Sql Server Interview Questions 90 2015 Administration Varshini Kutty
8 Foundations Of Sql Server 2008 60 2012 Optimization Sakunthala
9 Oracle Concurrency 205 2007 Security Pandurengan
12 Making Sense Of SQL 80 2009 Database Hari Krishnan

SQL BETWEEN Operator Example With Text Values

The following SQL statement selects all books with a AuthorName beginning with any of the letter BETWEEN 'E' and 'P', in the "Books" table:


SELECT * FROM Books 
WHERE 
AuthorName BETWEEN 'E' AND 'P';

The result of above query is:

BookId BookName BookPrice RelYear DomainName AuthorName
2 Troubleshooting SQL Server 70 2009 Optimization Keshavan
6 The Practical SQL Handbook 180 2014 Database Geetha
10 Practical SQL 160 2011 Administration Hari Krishnan
12 Making Sense Of SQL 80 2009 Database Hari Krishnan

It checks for text value:

  • Value1:- Greater than or equal
  • Value2:- Less than only

SQL NOT BETWEEN Operator Example With Text Values

To display the books outside the range of the previous example, use NOT BETWEEN:


SELECT * FROM Books 
WHERE 
AuthorName NOT BETWEEN 'E' AND 'V';

The result of above query is:

BookId BookName BookPrice RelYear DomainName AuthorName
3 The SQL Programming Language 84.22 2008 Optimization Dharan
4 Sql Server Interview Questions 90 2015 Administration Varshini Kutty
11 Oracle All-in-One For Dummies 199.97 2015 Programming Chandra

Search Keys

  • ms sql date between
  • sql query to retrieve data between two dates
  • between sql example
  • sql search between two dates
  • sql command between
  • sql between performance
  • sql query in between
  • date range sql
  • sql in range
  • sql range
  • sql date range
  • sql query range
  • sql between operator
  • sql between two dates
  • between and
  • between dates sql
  • between in sql
  • mssql between
  • between sql server