Understanding SQL Server Queries Involving NULL Values

By: Dusan Petkovic


A NULL in the CREATE TABLE statement specifies that a special value called NULL (which usually represents unknown or not applicable values) is allowed in the column. These values differ from all other values in a database. The WHERE clause of a SELECT statement generally returns rows for which the comparison evaluates to TRUE. The concern, then, regarding queries is, how will comparisons involving NULL values be evaluated in the WHERE clause?

All comparisons with NULL values will return FALSE (even when preceded by NOT). To retrieve the rows with NULL values in the column, Transact-SQL includes the operator feature IS NULL. This specification in a WHERE clause of a SELECT statement has the following general form:

0183_001

 

Example 1 shows the use of the IS NULL operator.

EXAMPLE 1

Get employee numbers and corresponding project numbers for employees with unknown jobs who work on project p2:

0183_002

 

The result is:

147

 

Because all comparisons with NULL values return FALSE, Example 2 shows syntactically correct, but logically incorrect, usage of NULL.

EXAMPLE 2

0183_003

 

The result is:

148_001

 

The system function ISNULL allows a display of the specified value as substitution for NULL (see Example 3).

EXAMPLE 3

0184_001

 

The result is:

148

 

Example 3 uses a column heading called task for the job column.

 

Leave a Reply