Test a Column for Null Values

Use is null in where, if, and while clauses to compare column values to NULL, and to select them or perform a particular action based on the results of the comparison.

Only columns that return a value of true are selected or result in the specified action; those that return false or unknown do not.

The following example selects only rows for which advance is less than 5000 or NULL:

select title_id, advance 
from titles 
where advance < 5000 or advance is null

SAP ASE treats null values in different ways, depending on the operators that you use and the type of values you are comparing. In general, the result of comparing null values is unknown, since it is impossible to determine whether NULL is equal (or not equal) to a given value or to another NULL. The following cases return true when expression is any column, variable or literal, or combination of these, which evaluates as NULL:

expression is null
expression = null
expression = @x where @x is a variable or parameter containing NULL. This exception facilitates writing stored procedures with null default parameters.
expression != n where n is a literal not containing NULL and expression evaluates to NULL.

The negative versions of these expressions return true when the expression does not evaluate to NULL:

expression is not null
expression != null
expression != @x

When the keywords like and not like are used instead of the operators = and !=, the opposite occurs. This comparison returns true:

expression not like null

While this comparison returns false:

expression like null

The far-right side of these expressions is a literal null, or a variable or parameter containing NULL. If the far-right side of the comparison is an expression (such as @nullvar + 1), the entire expression evaluates to NULL.

Null column values do not join with other null column values. Comparing null column values to other null column values in a where clause always returns unknown, regardless of the comparison operator, and the rows are not included in the results. For example, this query returns no result rows where column1 contains NULL in both tables (although it may return other rows):

select column1 
from table1, table2 
where table1.column1 = table2.column1

These operators return results when used with a NULL:

= returns all rows that contain NULL.
!= or <> returns all rows that do not contain NULL.

When set ansinull is on for SQL compliance, the = and != operators do not return results when used with a NULL. Regardless of the set ansinull option value, the following operators never return values when used with a NULL: <, <=, !<, >, >=, !>.

SAP ASE can determine that a column value is NULL. Thus, this is considered true:

column1 =  NULL

However, the following comparisons can never be determined, since NULL means “having an unknown value:”

where column1 > null

There is no reason to assume that two unknown values are the same.

This logic also applies when you use two column names in a where clause, that is, when you join two tables. A clause like “where column1 = column2” does not return rows where the columns contain null values.

You can also find null values or non-null values with this pattern:

where column_name is [not] null

For example:

where advance < 5000 or advance is null

Some of the rows in the titles table contain incomplete data. For example, a book called The Psychology of Computer Cooking (title_id = MC3026) has been proposed and its title, title identification number, and probable publisher have undetermined, null values in the price, advance, royalty, total_sales, and notes columns. Because null values do not match anything in a comparison, a query for all the title identification numbers and advances for books with advances of less than 5000 does not include The Psychology of Computer Cooking.

select title_id, advance 
from titles 
where advance < 5000

title_id  advance 
--------  ---------- 
MC2222         0.00 
PS2091     2,275.00 
PS3333     2,000.00 
PS7777     4,000.00 
TC4203     4,000.00 
 
(5 rows affected)

Here is a query for books with an advance of less than 5000 or a null value in the advance column:

select title_id, advance 
from titles 
where advance < 5000 
  or advance is null

title_id  advance 
--------  ---------- 
MC2222          0.00 
MC3026          NULL 
PC9999          NULL 
PS2091      2,275.00 
PS3333      2,000.00 
PS7777      4,000.00 
TC4203      4,000.00 
 
(7 rows affected)