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.
Related Links
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 |
Related Links