Null Values

A null value, or NULL, is not the same as 0 or blank. NULL means no entry has been made and usually implies that the value is either unknown or undefined. NULL allows you to distinguish between a deliberate entry of 0 (for numerical columns) or a blank (for character columns) and an unknown or inapplicable entry (NULL for both numerical and character columns). NULL indicates that the user did not explicitly make an entry or has explicitly entered NULL. For example, a null value in the price column of the titles table of the pubs sample database does not mean that the book is being given away free, it means that the price is not known or has not been set.

Syntax

In CREATE TABLE statements:
column_name datatype [NULL | NOT NULL]

In SELECT statements:
WHERE column_name IS [NOT] NULL

In INSERT statements:
VALUES({constant | DEFAULT | NULL}
[, {constant | DEFAULT | NULL}]...)

In UPDATE statements:
SET column_name = {expression | NULL}
[, column_name = {expression | NULL}...]

The ISNULL built-in function:
ISNULL(expression, value)

where

NULL

Indicates no entry has been made. The value is unknown (not blank or 0). In CREATE TABLE statements, specifying NULL after a column name means that if there is no default for this column, SQL Server assigns a null value whenever the user does not enter data for this column.

A user can enter NULL explicitly in an INSERT or UPDATE statement.

In SELECT statements, use IS [NOT] NULL to retrieve null values.

NOT NULL

Can also be used in a WHERE clause in any statement to retrieve all values except those that are NULL.

Remarks

If the user fails to make an entry in a column that allows null values, SQL Server supplies the value NULL (unless a default exists). A column defined with the keyword NULL also accepts an explicit entry of NULL from the user, no matter what datatype it is or if it has a default associated with it. Be careful when you enter NULL in character columns. If you put the value NULL inside single quotation marks ('NULL'), SQL Server interprets the entry as a character string rather than as the null value.

If an attempt to insert NULL is made against a column defined as NOT NULL, only the statement (INSERT or UPDATE) will be terminated. Any other statements within the batch will continue. In earlier releases of SQL Server, the entire batch was rejected at compile time. Although not recommended, the previous behavior of SQL Server (stopping the processing of the entire batch and rolling back any transactions) can be turned on with trace flag 243. For details, see the Trace Flags topic.

Defining columns as NULL provides a place holder for data you might not yet know. For example, in the titles table, price, advance, royalty, and ytd_sales are set up to allow null values. However, title_id and title do not allow null values because the lack of an entry in these columns would make the row meaningless and confusing. A price without a title makes no sense, but a title without a price simply means that the price is not available. When you create a table, define columns as NOT NULL when the information in the column is critical to the meaning of the other columns. When inserting data, the DEFAULT keyword can be used (for columns defined as allowing null values without a default) to insert NULL into a column. For details and examples, see the INSERT statement.

DEFAULT constraints defined on the table or user-defined defaults can be connected with either NULL or NOT NULL columns. For more information, see the CREATE TABLE statement, the ALTER TABLE statement, and the CREATE DEFAULT statement.

When you create NULL columns with certain datatypes, SQL Server automatically converts them to a different internal datatype to allow the storage of null values. SQL Server does not inform the user of the datatype change, and the user should be concerned about it only if querying the system tables.

A column definition that allows null values inserts NULL even when the rule does not explicitly specify NULL. For example, a rule defined on a column that allows null values and that states the values should be greater than 2 (@variable > 2) will insert NULL when no value is entered for the column.

An expression with an arithmetic or bitwise operator evaluates to NULL if any of the operands is null. For example, 1 + column1 evaluates to NULL if column1 is NULL.

An expression with a comparison operator evaluates to false if any of the operands is NULL. This means that null values never match another value (not even another NULL) when used with a comparison operator. For example, WHERE column1 = column2 will evaluate to false when column1 is NULL, when column2 is NULL, and when column1 and column2 are both NULL. Use IS [NOT] NULL to find null values in queries (when the columns being searched are defined as allowing null values); do not use = NULL. Use the ISNULL system function to replace null values in an expression with another value (if desired). If you try to find null values in columns defined as NOT NULL, SQL Server generates an error message.

The result of an expression with a nested subquery that returns no rows is NULL. For more information on null values in a subquery, see the Subqueries topic. The default result of a divide-by-zero action is to return NULL, return a message, and continue processing the batch. To change how the session handles divide-by-zero, see the SET statement.

Aggregate functions ignore null values, except COUNT(*), which includes them. For example, in the calculation AVG(advance), null values in the advance column are not counted, either in calculating the total amount or the number of values.

The aggregate functions MIN, MAX, SUM, and AVG return NULL if the parameter specified for them is an empty set (that is, no rows satisfy the WHERE clause).

NULLs are considered to be duplicates only in the case of a GROUP BY clause, ORDER BY clause, or when using the DISTINCT keyword and come first in the ORDER BY clause.

Null values are never joined or referenced (in the case of a FOREIGN KEY constraint), not even to other null values.

The empty string (' ') or (" ") is always stored as a single space in variables and column data. 'abc' ' ' 'def' is equivalent to 'abc def', but not to 'abcdef'. The empty string is never evaluated as NULL.

If you have two tables identical in structure except that one has NULL fields and some null values, and the other has NOT NULL fields, this difference makes it impossible to use SELECT to insert the data from the table with columns that allow null values into the table with columns that do not allow null values. If any of the data is NULL, you cannot update a field that does not allow NULLs by selecting from a field that allows NULLs.

If you specify NOT NULL when you create a column and do not create a default for it, an error message occurs whenever a user fails to make an entry in that column. In addition, the user cannot insert or update such a column with NULL as a value. The following table illustrates the relationship between the existence of a default (created as a default constraint or as a default) and the definition of a column as NULL or NOT NULL:

The sp_help system stored procedure reports on a column's nullability (whether or not the column accepts null values).

A column of the text or image datatype, when inserted with a null value, does not initialize a text pointer or contain a value. When a null value is written in a text or image column with the UPDATE statement, the column is initialized, a valid text pointer to the column is inserted into the table, and a 2K data page is allocated to the column. Once the column is initialized, it can be accessed by the READTEXT statement and the WRITETEXT statement. For more information, see the Datatypes topic and the text and image Manipulation topic.

Examples

A.    NULL Values with INSERT

The table created in this example shows two columns. The first will allow null values and the second will not. Because column t1 of table test is defined as accepting NULL in the CREATE TABLE statement, the three INSERT statements that follow are equivalent. The user can explicitly insert NULL, use the DEFAULT keyword, or allow the system to insert NULL. NULL is inserted in the t1 column whenever the user inserts rows but does not specify a value for the column.

CREATE TABLE test
(
    t1 char(10) NULL,
    t2 char(10) NOT NULL
)
INSERT test VALUES(NULL, 'Stuff')
INSERT test VALUES(DEFAULT, 'Stuff')
INSERT test(t2) VALUES ('Stuff')

B.    NULL Values with SELECT

The SELECT statement retrieves all rows from test and displays null values in column t1 with the value unknown.

SELECT Col1 = ISNULL(t1, 'unknown'), Col2 = t2
    FROM test
go

Col1       Col2       
---------- ---------- 
unknown    Stuff      
unknown    Stuff      

(2 row(s) affected)

C.    NULL Values with UPDATE

This update uses NULL to define the value for the advance column in the titles table. Notice that quotation marks are not used when specifying NULL.

UPDATE titles
    SET advance = NULL
    WHERE title_id = 'TC3218'

D.    NULL Values in a WHERE Clause

SELECT title_id, advance
    FROM titles
    WHERE advance < $5000 OR advance IS NULL
go

title_id advance                    
-------- -------------------------- 
MC2222   0.00                       
MC3026   NULL                     
PC9999   NULL                     
PS2091   2,275.00                   
PS3333   2,000.00                   
PS7777   4,000.00                   
TC3218   NULL                     
TC4203   4,000.00                   

(8 row(s) affected)

See Also