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

IN, NOT IN (U-SQL)

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

U-SQL provides the IN and NOT IN comparison operators to test for membership in a set of values. It returns true if the value is equal to at least one value in the list, false otherwise for IN and true if the value is not in the list and false otherwise for NOT IN.

The types of the values to be tested and the members of the test set have to be a string or numeric type or null, and have to be compatible with each other, otherwise an error is raised. The comparison operation is using C# equality semantics and string comparisons are culture invariant.

U-SQL does not support the subquery form of the SQL IN operator. Most subquery IN operations can be transformed to use SEMIJOIN.

Syntax
IN_Expression :=                                                                                         
    expression ['NOT'] 'IN' '(' Expression_List ')'.

Semantics of Syntax Elements

  • expression
    is the expression that creates the value to be tested. It needs to be null or of a string or numeric type, otherwise an error is raised.

  • Expression_List
    is list of expressions that identify the values of the test set. The maximal supported number of items in the list is 20000.

    Syntax
    Expression_List :=                                                                                  
        expression {',' expression}.
    

    The types of the values to be tested and the members of the test set have to be to be null or of a string or numeric type and compatible with the value to be tested, 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 DeptID IN (100, 400);

OUTPUT @result TO "/ReferenceGuide/Operators/Logical/In1.txt" USING Outputters.Csv();


@result =
    SELECT * FROM @data
    WHERE DeptID NOT IN (100, 400);

OUTPUT @result TO "/ReferenceGuide/Operators/Logical/NotIn1.txt" USING Outputters.Csv();


@result =
    SELECT * FROM @data
    WHERE EmpName IN ("Noah", "Jacob", "Ava");

OUTPUT @result TO "/ReferenceGuide/Operators/Logical/In2.txt" USING Outputters.Csv();

See Also

© 2018 Microsoft