BETWEEN operator allows to compare a value against a data type specific range and returns true if the value is within the range given by the lower and upper bounds inclusive both boundaries, false otherwise. The types of the expressions have to be compatible or an error is raised.
a BETWEEN b AND c is equivalent to b <= a AND a <= c. Since the rewrite of BETWEEN will execute the expression a twice, it should be a deterministic expression.
Note that the comparison is using C# comparison semantics, especially regarding
BETWEEN_Expression := expression 'BETWEEN' Lowerbound 'AND' Upperbound.
Lowerbound := expression.
Upperbound := expression.
is the expression that returns the value that gets compared against the range. The type of the expression has to be a comparable type, otherwise an error is raised. The expression should be deterministic because it will be executed twice in the rewrite..
- The examples can be executed in Visual Studio with the Azure Data Lake Tools plug-in.
- The scripts can be executed locally. An Azure subscription and Azure Data Lake Analytics account is not needed when executed locally.
@data = SELECT * FROM (VALUES (1, "Noah", 100, (int?)10000, new DateTime(2012,05,31)), (2, "Sophia", 100, (int?)15000, new DateTime(2012,03,19)), (3, "Liam", 100, (int?)30000, new DateTime(2014,09,14)), (6, "Emma", 200, (int?)8000, new DateTime(2014,03,08)), (7, "Jacob", 200, (int?)8000, new DateTime(2014,09,02)), (8, "Olivia", 200, (int?)8000, new DateTime(2013,12,11)), (9, "Mason", 300, (int?)50000, new DateTime(2016,01,01)), (10, "Ava", 400, (int?)15000, new DateTime(2014,09,14)), (11, "Ethan", 400, (int?)null, new DateTime(2015,08,22)) ) AS T(EmpID, EmpName, DeptID, Salary, StartDate); @result = SELECT * FROM @data WHERE EmpID BETWEEN 3 AND 10; OUTPUT @result TO "/ReferenceGuide/Operators/Logical/Between1.txt" USING Outputters.Csv(); // alternative @result = SELECT * FROM @data WHERE EmpID >= 3 AND EmpID <= 10; OUTPUT @result TO "/ReferenceGuide/Operators/Logical/Between2.txt" USING Outputters.Csv(); @result = SELECT * FROM @data WHERE StartDate BETWEEN DateTime.Parse("2012/03/15") AND DateTime.Parse("2012/05/31"); OUTPUT @result TO "/ReferenceGuide/Operators/Logical/Between3.txt" USING Outputters.Csv();