IS [NOT] NULL

Determines if a query expression is null.

expression IS [ NOT ] NULL

Arguments

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

Return Value

true if expression returns null; otherwise, false.

Remarks

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.

Example

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

See Also

Concepts

Entity SQL Reference