Table of contents
TOC
Collapse the table of content
Expand the table of content

BETWEEN (U-SQL)

Michael Rys|Last Updated: 5/15/2018
|
4 Contributors

The 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 null values.

Syntax
BETWEEN_Expression :=                                                                                    
     expression 'BETWEEN' Lowerbound 'AND' Upperbound.
Lowerbound := expression.
Upperbound := expression.

Semantics of Syntax Elements

  • 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..

  • Lowerbound
    is the expression that returns the value of the lower bound. The type of the expression has to be compatible with the expression type, otherwise an error is raised.

  • Upperbound
    is the expression that returns the value of the upper bound. The type of the expression has to be compatible with the expression type, otherwise an error is raised.

Return Type

bool

Examples

  • 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();

See Also

© 2018 Microsoft