IS NULL (U-SQL)

Article
09/27/2017

Summary

Determines whether a specified expression is NULL. If the value of expression is NULL, IS NULL returns TRUE; otherwise, it returns FALSE. If the value of expression is NULL, IS NOT NULL returns FALSE; otherwise, it returns TRUE.

Syntax

Is_Null_Predicate :=                                                                                     
     string_expression 'IS' ['NOT'] 'NULL'.

Remarks

string_expression
is the expression that creates the string value to be tested.
NOT
specifies that the Boolean result be negated. The predicate reverses its return values, returning TRUE if the value is not NULL, and FALSE if the value is NULL.

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.
The examples below are based on the dataset defined below. Ensure your execution includes the rowset variable.

Dataset

@somePeople = 
    SELECT * FROM 
        ( VALUES
        (1, "Noah",    100, (int?)10000, new DateTime(2012,05,31), "cell:030-0074321,office:030-0076545"),
        (2, "Sophia",  100, (int?)15000, new DateTime(2012,03,19), "cell:(5) 555-4729,office:(5) 555-3745"),
        (3, "Liam",    100, (int?)30000, new DateTime(2014,09,14), ""),
        (6, "Emma",    200, (int?)8000,  new DateTime(2014,03,08), (string)null),
        (7, "Jacob",   200, (int?)8000,  new DateTime(2014,09,02), "cell:(5) 555-3932"),
        (8, "Olivia",  200, (int?)8000,  new DateTime(2013,12,11), "cell:88.60.15.31,office:88.60.15.32"),
        (10, "",       400, (int?)15000, new DateTime(2014,09,14), "cell:91.24.45.40,office:91.24.45.41"),
        (11, "Ethan ", 400, (int?)null,  new DateTime(2015,08,22), "cell:(604) 555-4729,office:(604) 555-3745")
        ) AS T(EmpID, EmpName, DeptID, Salary, StartDate, PhoneNumbers);

IS NULL

@result = 
    SELECT * FROM @somePeople 
    WHERE Salary IS NULL;

OUTPUT @result 
TO "/Output/ReferenceGuide/Predicates/IsNull.txt" 
USING Outputters.Tsv();

IS NOT NULL

@result = 
    SELECT * FROM @somePeople
    WHERE Salary IS NOT NULL;

OUTPUT @result 
TO "/Output/ReferenceGuide/Predicates/IsNotNull.txt" 
USING Outputters.Tsv();

Share via