When you see NULL in a column, it means that the user or application has made no entry in that column. A data value for the column is unknown or not available.
NULL is not synonymous with zero (a numerical value) or blank (a character value). Rather, null values allow you to distinguish between a deliberate entry of zero (for numeric columns) or blank (for character columns) and a non-entry (NULL for both numeric and character columns).
NULL can be entered in a column for which null values are permitted (as specified in the CREATE TABLE statement) in three ways:
When null values are retrieved, the query results show the word NULL in the appropriate position. For example, the advance column of the titles table allows null values. By inspecting the data in that column, you can tell whether a book had no advance payment (zero in the advance column, as in the row for MC2222) or whether the advance amount was not known when the data was entered (NULL in the advance column, as in the row for MC3026):
SELECT title_id, type, advance |
|||||
FROM titles |
|||||
WHERE pub_id = '0877' |
|||||
title_id |
type |
advance |
|||
-------- |
---------- |
----------- |
|||
MC2222 |
mod_cook |
0.00 |
|||
MC3021 |
mod_cook |
15,000.00 |
|||
MC3026 |
UNDECIDED |
(null) |
|||
PS1372 |
psychology |
7,000.00 |
|||
TC3218 |
trad_cook |
7,000.00 |
|||
TC4203 |
trad_cook |
4,000.00 |
|||
TC7777 |
trad_cook |
8,000.00 |
|||
(7 row(s) affected) |
|||||
When you compare a null value with another value, the result is never true; a null value does not match anything, not even another null value. So, for example, the following expression is false and will not return results:
ytd_sales > NULL
Null values do not match each other because there is no reason to assume that two unknown values are the same. Null values never join, not even to other null values. (For information about joining tables with null values, see Using Joins to Create Multitable Queries).
Null values are considered to be equal only for the purposes of the ORDER BY, GROUP BY, and DISTINCT keywords. Also, a unique index or UNIQUE constraint that allows NULL may contain only one row containing NULL. A subsequent row with NULL will be rejected. A primary key may not have NULL in any column that is part of the key.
Computations involving NULL evaluate to NULL because the result must be unknown if any of the factors is unknown. For example, column1 1 evaluates to NULL if column1 is NULL.
When the columns being searched include ones defined as allowing null values, you can find null or non-null values in the database with this pattern:
WHERE column_name IS [NOT] NULL
If you try to find null or non-null values in columns defined as NOT NULL, SQL Server displays an error message.
Some of the rows in the titles table contain incomplete data. For example, a book called The Psychology of Computer Cooking has been proposed and its title, title identification number, and probable publisher entered. However, since the author has no contract yet and details are undecided, null values appear in the price, advance, royalty, ytd_sales, and notes columns. Because null values don't match anything in a comparison, a query for all the title identification numbers and advances for books with moderate advances (under $5000) will not find the row for The Psychology of Computer Cooking, title_id MC3026.
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 row(s) affected) |
||
To include books with an advance under $5000 or a null value in the advance column, use this query:
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 row(s) affected) |
|||