Use is null in where, if, and while clauses (discussed in Chapter 15, “Using Batches and Control-of-Flow Language”) 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
Adaptive Server 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: <, <=, !<, >, >=, !>.
Adaptive Server 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)
See Chapter 8, “Creating Databases and Tables,” for information on NULL in the create table statement and for information on the relationship between NULL and defaults. See Chapter 7, “Adding, Changing, Transferring, and Deleting Data,” for information on inserting null values into a table.
