Export (0) Print
Expand All

IS [NOT] NULL

Determines if a query expression is null.

expression IS [ NOT ] NULL

expression
Any valid query expression. Cannot be a collection, have collection members, or a record type with collection type properties.

NOT
Negates the EDM.Boolean result of IS NULL.

true if expression returns null; otherwise, false.

Use IS NULL to determine if the element of an outer join is null:

select c 
      from LOB.Customers as c left outer join LOB.Orders as o 
                              on c.ID = o.CustomerID  
      where o is not null and o.OrderQuantity = @x

Use IS NULL to determine if a member has an actual value:

select c from LOB.Customer as c where c.DOB is not null

The following table shows the behavior of IS NULL over some patterns. All exceptions are thrown from the client side before the provider gets invoked:

 

Pattern Behavior

null IS NULL

Returns true.

TREAT (null AS EntityType) IS NULL

Returns true.

TREAT (null AS ComplexType) IS NULL

Throws an error.

TREAT (null AS RowType) IS NULL

Throws an error.

EntityType IS NULL

Returns true or false.

ComplexType IS NULL

Throws an error.

RowType IS NULL

Throws an error.

The following Entity SQL query uses the IS NOT NULL operator to determine if a query expression is not null. The query is based on the AdventureWorks Sales Model. To compile and run this query, follow these steps:

  1. Follow the procedure in How to: Execute a Query that Returns StructuralType Results.

  2. Pass the following query as an argument to the ExecuteStructuralTypeQuery method:


SELECT VALUE product FROM AdventureWorksEntities.Products
AS product WHERE product.Color IS NOT NULL
    




Build Date:

2012-10-01
Show:
© 2014 Microsoft