Partager via

IN, NOT IN (U-SQL)

  • Article

Summary

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 ')'.

Remarks

  • 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