15.00 - IS NULL / IS NOT NULL - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)

IS NULL / IS NOT NULL

Purpose  

Searches for or excludes nulls in an expression.

Syntax  

where:

 

Syntax element …

Specifies …

expression

an expression that specifies a value that is tested for nulls.

ANSI Compliance

IS NULL and IS NOT NULL are ANSI SQL:2011 compliant.

Example  

To search for the names of all employees who have not been assigned to a department, enter the following statement:

   SELECT Name 
   FROM Employee 
   WHERE DeptNo IS NULL;

The result of this query is the names of all employees with a null in the DeptNo field.

Example  

Conversely, to search for the names of all employees who have been assigned to a department, you could enter the following statement:

   SELECT Name 
   FROM Employee 
   WHERE DeptNo IS NOT NULL;

This query returns the names of all employees with a non-NULL value in the DeptNo field.

Example : Searching for NULL and NOT-NULL in the Same Statement

If you are searching for NULLs and non-NULL values in the same statement, the search condition for the NULLs must appear separately.

For example, to select the names of all employees without the job title of “Manager” or “Vice Pres”, plus the names of all employees with a null in the JobTitle column, you must enter the statement as follows:

   SELECT Name, JobTitle 
   FROM Employee 
   WHERE (JobTitle NOT IN ('Manager' OR 'Vice Pres')) 
   OR (JobTitle IS NULL) ;

Example : Searching a Table That Might Contain Nulls

You must be careful when searching a table that might contain nulls. For example, if the EdLev column contains nulls and you submit the following query, the result contains only the names of employees with an education level of less than 16 years.

   SELECT Name, EdLev 
   FROM Employee 
   WHERE (EdLev < 16) ;

To ensure that the result of a statement contains nulls, you must structure it as follows.

   SELECT Name, EdLev 
   FROM Employee 
   WHERE (EdLev < 16) 
   OR (EdLev IS NULL) ;