ABAP Keyword Documentation →  ABAP − Reference →  Processing External Data →  ABAP Database Access →  ABAP SQL →  ABAP SQL - Operands and Expressions →  ABAP SQL - SQL Conditions sql_cond →  sql_cond - rel_exp for Statements 

sql_cond - IS NULL

Other versions: 7.31 | 7.40 | 7.54

Syntax

... operand IS [NOT] NULL ...

Effect

This relational expression is true if the value of the operand operand is (is not) the null value.

The following applies for operand:

• SQL expressions except for aggregate expressions and window expressions can be specified.

• In a HAVING clause, aggregate expressions can also be used.

• Unlike in other relational expressions, the data type can also be STRING, RAWSTRING, or GEOM_EWKB.

Notes

• The relational expression IS [NOT] NULL is the only expression for which the result is true or false when the operand is given the null value. The result is unknown for all other possible relational expressions in a condition sql_cond when one of the operands in question is given the null value. More specifically, this is relevant for expressions specified as operands when their result is the null value.

• Null values can also be evaluated using a null indicator.

• IS [NOT] NULL can be used to check LOBs and geodata types. As in all other relational expressions, operands of the data types LCHR and LRAW are not possible.

Example

Compares the results of an inner and a left outer join. The row with null values produced by the left outer join is removed again by the WHERE condition with IS NOT NULL, so the results are the same.

DELETE FROM demo_join1. 
INSERT demo_join1 FROM TABLE @( VALUE #( 
  ( a = 'a1' b = 'b1' c = 'c1'  d = 'd1' ) 
  ( a = 'a2' b = 'b2' c = 'c2'  d = 'd2' ) ) ). 

DELETE FROM demo_join2. 
INSERT demo_join2 FROM TABLE @( VALUE #( 
  ( d = 'd1' e = 'e1' f = 'f1'  g = 'g1'  h = 'h1' ) ) ). 

SELECT * 
       FROM demo_join1 AS d1 
         INNER JOIN demo_join2 AS d2 
           ON d1~d = d2~d 
       INTO TABLE @DATA(result1). 

SELECT * 
       FROM demo_join1 AS d1 
         LEFT OUTER JOIN demo_join2 AS d2 
           ON d1~d = d2~d 
       WHERE d2~d IS NOT NULL 
       INTO TABLE @DATA(result2). 

ASSERT result1 = result2.