Skip to Content
Technical Articles
Author's profile photo Lena Padeken

Special ABAP SQL expression NULL

A null value is a special value returned by a database to indicate an undefined value or result. There are no special null values in ABAP, and it’s not easy to add such a feature to the language these days. The value range of a data type takes up the entire memory space of its data object. Imagine, for example, the simple one byte integer type INT1 from the ABAP Dictionary, corresponding to b in ABAP: The value range is 0 to 255, which is exactly the range you can express with bits of 1 byte (hexadecimal 00 to FF). There’s no space left for a null value and no value left to represent a null value.

When a null value is read with ABAP SQL from the database, it’s simply converted to the type-specific initial value of the ABAP target field, which is 0 for numeric types and blank for character types. So there’s no way to tell if a null value was read by a simple comparison. For a long time, the only way to handle null values was the relational expression IS NULL and, more recently, null indicators.

In fact, in the early ABAP days, this was never seen as a problem because in the ABAP world, null values only appeared in the database in exceptional cases:

  • In ABAP SQL, write accesses to database tables do not normally produce null values.
  • Null values can be produced in DDIC database tables when new columns are appended to populated tables. However, this can be overridden with a flag for initial values.

In terms of read accesses, it was only with the introduction of outer joins in ABAP Release 4.0 that null values began to appear in result sets. From then on, as more and more SQL expressions and functions were added, null values appeared as results of these expressions and functions. A prominent example being case distinctions: If you omit the ELSE statement, the expression will automatically produce the null value as a result. Other examples are functions that return null values when an exception occurs. For these reasons, null values are becoming more common in ABAP SQL and there is a need to improve the handling of null values.

In addition to the relational expression IS NULL and the null indicators mentioned above, there is a new way of explicitly expressing a null value in ABAP SQL, namely the expression NULL.

While ABAP CDS introduced ELSE NULL with ABAP Release 7.89, SAP BTP ABAP Environment 2208 (see feature matrix of all CDS DDL features), which allows you to specify a null value after ELSE of a CASE expression, ABAP SQL goes a step further by introducing a new SQL expression NULL that can be used in many more operand positions of ABAP SQL. The special SQL expression NULL is available since ABAP Release 7.83, SAP BTP ABAP Environment 2102.

This blog gives a brief introduction to the null expression, and since NULL can be used almost anywhere in the ABAP SQL environment, we’ve tested every position and summarized our findings and possible pitfalls.


The operand NULL represents the null value and can be used at many operand positions of SQL expressions. A famous example is the case distinction introduced above.

Keep in mind that ABAP does not have null values. Rather, null values that are assigned to an ABAP data object in ABAP SQL, Native SQL, or AMDP are converted to type-dependent initial values. Read more here.

Thus, the expression NULL is an option to handle null values in ABAP SQL. Other options are the relational expression IS NULL and null indicators. The difference between NULL and IS NULL is explained below. One important aspect of using NULL is that it must be possible to derive a type (see Pitfalls).

Introducing NULL, the goal was to allow NULL as a constant column value in the field list. In the end, NULL was generally allowed in ABAP SQL. This makes it possible to set an entire expression to null by specifically using NULL for one of its operands depending on the condition. Possible use cases are explained below.

Use case

Two possible use cases of the null expression are ELSE NULL and THEN NULL, both of which are part of case distinctions. Another example of case distinctions is given in the Scope section to demonstrate the operand positions in which NULL can be used. The following sample code shows an example of all flights in March 2023 with the airline code LH. There’s a case distinction included to identify flights with available first class seats.

METHOD main.
    SELECT FROM sflight
           FIELDS carrid,
                     WHEN seatsocc_f < seatsmax_f THEN 'yes'
                     WHEN seatsocc_f = seatsmax_f THEN CAST( NULL AS CHAR( 1 ) )
                  END AS book_a_seat
           WHERE fldate BETWEEN datn`20230301` AND datn`20230331`
              AND carrid = 'LH'

    cl_demo_output=>display( result ).

(By the way, do you recognize the typed literals in the example? Check out this blog post.)

The query returns the following (excluding NULL_IND):

LH 0401 2023-03-14 668.2 EUR 767-200
LH 0402 2023-03-10 668.2 EUR A380-800 yes
LH 2402 2023-03-15 244.2 EUR A380-800 yes
LH 2407 2023-03-15 244.2 EUR A320-200
LH 0400 2023-03-15 808.52 EUR A340-600

The use case for NULL here is that you’re mainly interested in the available seats, but would still like to know about other flights, perhaps for planning a flight in the coming months. Everything about NULL and the null indicator is explained below.


The following example shows where NULL can be used in a simple case distinction. You can find an example like this in the ABAP Keyword Documentation.

METHOD main.
    SELECT FROM scarr
             LEFT OUTER JOIN spfli
               ON scarr~carrid = spfli~carrid
           FIELDS scarr~carrid,
                  CASE distid
                     WHEN 'MI' THEN 'Miles'
                     WHEN 'KM' THEN 'Kilometers'
                     ELSE NULL
                  END AS distance,
                  CASE distid
                     WHEN 'MI' THEN NULL
                     WHEN NULL THEN 'Kilometers'
                     ELSE NULL
                  END AS null
           INTO TABLE @FINAL(result)
           UP TO 5 ROWS.

    cl_demo_output=>display( result ).

In this example, two simple case distinctions are specified that both contain NULL. NULL can be used explicitly in the operand position after ELSE, but also after WHEN and THEN. The result table looks like this:

AA MI Miles
AA MI Miles
AZ MI Miles

When looking at the result table, you cannot see whether null values are present or not. This is because null values are converted to initial values and are represented by blanks, for example. Null indicators can be used to identify null values:


Using null indicators, columns of the result set containing the null value can be determined. In this example, a structured indicator is used and each component of the structure is of type x with length 1. A component value of hexadecimal 1 denotes a null value in the respective column of the result set. The result table now looks like this:

Result table with a null indicator

The result table emphasizes that the columns DISTID, DISTANCE, and NULL return the null value for all comparisons that are not true. Additionally, the column NULL returns an unknown result for comparisons with the null value. While NULL is passed to the result table, null values are converted to initial values.


Basically, NULL can be used at any operand position of an SQL expression. The following table marks exceptions. The column True/False indicates if NULL can be used (True) or if NULL cannot be used (False) at a specific operand position.

Category Syntax Argument True/False
Numeric function ROUND( sql_exp, pos ) pos True
String function LIKE_REGEXPR( pcre = pcre, value = sql_exp[, case_sensitive = case] ) pcre
LOCATE( sql_exp, sub[ ... ] ) sub True
LOCATE_REGEXPR( pcre = pcre, value = sql_exp[, case_sensitive = case][ ... ] ) pcre
LOCATE_REGEXPR_AFTER( pcre = pcre, value = sql_exp[, case_sensitive = case][ ... ] ) pcre
OCCURRENCES_REGEXPR( pcre = pcre, value = sql_exp[, case_sensitive = case] ) pcre
REPLACE_REGEXPR( pcre = pcre, value = sql_exp1, with = sql_exp2[, case_sensitive = case][ ... ] ) pcre
SUBSTRING_REGEXPR_AFTER( pcre = pcre, value = sql_exp[ ... ][, case_sensitive = case][ ... ] ) pcre
Unit conversion UNIT_CONVERSION( quantity = a1, source_unit = a2, target_unit = a3[, client = a4 ][ ... ] ) a2
Currency conversion CURRENCY_CONVERSION( amount = a1, source_currency = a2, target_currency = a3[, exchange_rate_date = a4 ][, exchange_rate_type = a5 ][, client = a6 ][ ... ] ) a2
Window function FIRST_VALUE( col|sql_null ) sql_null True
LAST_VALUE( col|sql_null ) sql_null True
Relational expression BETWEEN operand1 AND operand2 operand1

Most positive exceptions occur for string functions as valid argument type for pcre, sub, and case. The window functions are mentioned here because only columns were allowed as argument before NULL was introduced.


As mentioned above, the two most important take-aways for using NULL are to remember that it must be possible to derive a type and that the null expression can be specified at almost all SQL expression positions with a few exceptions. If NULL is used in a relational expression, the result is never true but unknown. Unknown is similar to false but the behavior is different for AND, OR, and NOT. Additionally, another possible pitfall comes to light when testing error handling with, for example, the UNIT_CONVERSION() function.

Code Error type Error message
  quantity = dec3,
  source_unit = cast( null as unit ),
  target_unit = unit`KM`,
  on_error = @sql_unit_conversion=>c_on_error-set_to_null )
Static It is not possible to derive a type for the NULL value from its context.
  quantity = dec3,
  source_unit = unit`MI`,
  target_unit = null,
  on_error = @sql_unit_conversion=>c_on_error-set_to_null )
Dynamic A catchable exception of class CX_SY_OPEN_SQL_DB is raised.

Based on the first example, you can assume that you need to specify a length for types that do not have a fixed length. For example, writing CAST( NULL AS CHAR( 10 ) ). This is true and you need to specify the length for all types with a variable length for casts with NULL. The second example shows the dynamic error handling of NULL. Further, as introduced above, the expression NULL looks similar to the relational expression IS NULL. Important to note is that while NULL is an expression, IS NULL is a relational expression with predicate syntax.

Further Information

The SQL expression NULL handles null values and can be specified at most operand positions in an SQL expression. NULL is type-dependent and needs a specified length for types with variable lengths in a cast expression. Do not confuse the relational expression with the predicate syntax IS NULL with the expression NULL. Keeping everything in mind, you can start using NULL. Some further references are listed below:

You should now know how to use the SQL expression NULL in your projects. Use NULL for null handling in ABAP SQL. The examples given in this blog are intended for demonstration purposes only. Are you excited to use the special null expression? Write your thoughts in the comment section. Don’t miss out on new language elements and follow my profile (Lena Padeken) for similar blog posts.

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Shai Sinai
      Shai Sinai

      I must confess that the fact that the ABAP language itself doesn't include NULLs makes all these solutions a little cumbersome (even the NULL structure) since any conversion from/to DB requires an explicit handling.

      A better solution could have been introducing WITH NULL INDICATOR in field (elementary type) level which could have made the null handling quite transparent (with new ABAP statements like IS NULL and SET NULL, for example).

      Author's profile photo Christoph Weyd
      Christoph Weyd

      NULL does NOT exist but you can use CLEAR variable and IF variable IS INITIAL which does basically the same.

      Author's profile photo Shai Sinai
      Shai Sinai

      For the matter of fact, as explained in the post, this is actually incorrect:

      Initial and NULL values aren't the same (e.g. Integer value 0 is initial, but not NULL).

      Author's profile photo Horst Keller
      Horst Keller

      See!ABAP_ALTERNATIVE_2@2@ .


      This is your elementary field.

      Nevertheless, handling is not simpler. But it safes memory.

      New ABAP statements like IS NULL and SET NULL are not possible as explained in the first paragraph of the blog.