Between

Table of Contents



BETWEEN matches values that fall within a range, and NOT BETWEEN matches values outside of it. The values are compared as numbers.
expression BETWEEN low AND high
expression NOT BETWEEN low AND high

Edge of range inclusion notice

The range includes the low and high values: Id BETWEEN 2 AND 4 matches 2, 3 and 4.

  • The low and high values can be numbers, fields, variables or expressions.
  • A document whose value is missing (null) matches neither BETWEEN nor NOT BETWEEN.
  • A value that is not a number fails with an error.
  • BETWEEN can be used in join ON conditions, and uses an index on the field when one is available.


round-pushpin Selects words whose Id is between 2 and 4.
SELECT * FROM WordList:Word WHERE Id BETWEEN 2 AND 4

round-pushpin Selects words whose Id is not between 2 and 13.
SELECT * FROM WordList:Word WHERE Id NOT BETWEEN 2 AND 13

round-pushpin Combines BETWEEN with other conditions. The AND inside BETWEEN separates the low and high values; the next AND connects another condition.
SELECT * FROM WordList:Word WHERE Id BETWEEN 1 AND 10 AND LanguageId = 1

round-pushpin Uses expressions and a variable for the range.
DECLARE @First = 2
SELECT * FROM WordList:Word WHERE Id BETWEEN @First AND @First + Length('abc')




SQL :: Expressions
Literals, operators, comments and variables in KBSQL.
SQL :: Inner Join
Combines documents from two or more schemas based on matching fields.
SQL :: IsBetween
Returns true (1) if the value is within the given range, including the range boundaries.
SQL :: IsNotBetween
Returns true (1) if the value is outside of the given range. The range boundaries are considered inside the range.
SQL :: Keywords
A breakdown of all of the statements and keywords supported by the query processor.
SQL :: Left Outer Join
Combines documents from two or more schemas, keeping documents that have no match.
SQL :: Like
Allows basic pattern matching in a where clause.
SQL :: Not
The NOT keyword negates LIKE and BETWEEN.
SQL :: Select
Select statements are used to read (or query) data from the database.
SQL :: Where
The where clause filters the documents read, updated or deleted by a statement.