What is isnull Alternative in Teradata SQL?

The relational databases such as SQL server supports isnull function. It returns the alternate value if input argument or expression is null. But, Teradata does not support isnull function. Instead, you have to use an alternate method to replace null values. In this article, we will check what is isnull alternative in Teradata SQL with some examples.

isnull Function Alternative in Teradata SQL

As mentioned earlier, Teradata does not support isnull function. There are many alternative methods that you can use as an alternative to isnull function.

The SQL Server ISNULL() function lets you return an alternative value when an expression is NULL.

isnull Function Alternative Methods in Teradata

Following are methods that you can use as an alternative method.

• Teradata NVL Function
• COALESCE Function in Teradata
• CASE Statement

Let us check these methods in brief.

Teradata NVL as a ISNULL Function Alternative

Teradata NVL functions replaces a NULL value with a numeric or a string value. In other word, the NVL function replaces NULL values with a replacement string that you provide as a function argument.

For example, consider following NVL example to replace null values.

SELECT qty,
       NVL (qty, 0) new_qty
FROM   (SELECT NULL AS qty) AS a;


        qty      new_qty
-----------  -----------
          ?            0

As you can see, NVL function return non-null value. In short, it return non-null value.

Related Article,

• Teradata NVL and NVL2 Functions and Examples

Teradata COALESCE as a ISNULL Alternative

The SQL Coalesce function evaluates the expressions in an order and always returns first non-null value from the defined argument list.

The SQL Coalesce and IsNull functions are used to handle NULL values. During the expression evaluation process the NULL values are replaced with the user-defined value.

For example, consider following COALESCE function example to replace null values.

SELECT qty,
       COALESCE (qty, NULL, 0) new_qty
FROM   (SELECT NULL AS qty) AS a;

        qty      new_qty
-----------  -----------
          ?            0

The COALESCE function replaces NULL with manual value. In other words, it works as an IF-ELSE statement.

Teradata CASE Statement as ISNULL Alternative

The CASE statement in Teradata is a conditional statement which returns a value on a particular condition.

For example, consider following example

SELECT qty,
       CASE WHEN qty is NULL THEN 0 ELSE qty END as new_qty
FROM   (SELECT NULL AS qty) AS a;

        qty      new_qty
-----------  -----------
          ?            0

You need to provide condition to test for null values. Moreover, CASE statement can be used as an IF-ELSE statement.

Hope this helps 🙂