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.

You can also search these topics using sql between dates, sql server between, between clause in sql, how to use between in sql, sql server between date, sql between inclusive, 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 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

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

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

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

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
You can also search these topics using 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, sql between operator, sql between two dates, between dates sql, between sql server.