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:
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:
The result is:
Because all comparisons with NULL values return FALSE, Example 2 shows syntactically correct, but logically incorrect, usage of NULL.
EXAMPLE 2
The result is:
The system function ISNULL allows a display of the specified value as substitution for NULL (see Example 3).
EXAMPLE 3
The result is:
Example 3 uses a column heading called task for the job column.
Leave a Reply