IS [NOT] NULL (Entity SQL)

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 Boolean result of IS [NOT] 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 (EntityClient).

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

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

The output is shown below:

ProductID: 317
Name: LL Crankarm
ProductNumber: CA-5965
MakeFlag: False
ProductID: 318
Name: ML Crankarm
ProductNumber: CA-6738
MakeFlag: False
ProductID: 319
Name: HL Crankarm
ProductNumber: CA-7457
MakeFlag: False
ProductID: 320
Name: Chainring Bolts
ProductNumber: CB-2903
MakeFlag: False
ProductID: 321
Name: Chainring Nut
ProductNumber: CN-6137
MakeFlag: False
...

See Also

Concepts

Entity SQL Reference

Other Resources

Comparison Operators