NULL in Teradata

NULL represents missing or unknown value in the column. In other words, NULL is a place holder indicating that no value is present. Teradata allows to create the table with DEFAULT value as NULL for a column. The NULL will be placed for those columns if the user didn’t insert the value for the particular column.

Create table with Default value as NULL in Teradata

Lets create the User_Comments table in Teradata to show the Default value as NULL

In some cases, Users will not enter the comments for the articles that are written in our website. So the comments column will have the NULL values for those users.

Lets see this scenario with examples as below. In the first Insert statement, we are adding the values only for User_Id and User_Name.

Then the second Insert statement is adding the values for all three columns including comments.

Since we have mentioned the Default value as NULL for comments column, Teradata adds the NULL for the missing value (refer the first Insert statement above).

Selecting the NULL values in Teradata

If we specify the Column Name equals to NULL in the where condition, we will receive an error in Teradata as WHERE NULL = NULL is not valid because it can never be true.

Instead of this , we need to use like WHERE Column_Name IS NULL in the select query to fetch columns which contains NULL values in it.

This SELECT query returned the rows which has the comments as NULL in the User comments table.

Selecting the NOT NULL values in Teradata

We need to specify the condition like WHERE Column_Name IS NOT NULL in the SELECT query to fetch the column which doesn’t contain the NULL values in it.

This SELECT query excluded the rows which has the comments as NULL in the User comments table.

Recommended Articles

• NULLIFZERO function in Teradata with examples