The NULL value specifies a value that is unknown or not applicable.
NULL
NULL is a special value that is different from any valid value for any data type. However, the NULL value is a legal value in any data type. NULL is used to represent missing or inapplicable information. There are two separate and distinct cases where NULL is used:
Situation | Description |
---|---|
missing | The field does have a value, but that value is unknown. |
inapplicable | The field does not apply for this particular row. |
SQL allows columns to be created with the NOT NULL restriction. This means that those particular columns cannot contain NULL.
The NULL value introduces the concept of three valued logic to SQL. The NULL value compared using any comparison operator with any value (including the NULL value) is "UNKNOWN." The only search condition that returns TRUE is the IS NULL predicate. In SQL, rows are selected only if the search condition in the WHERE clause evaluates to TRUE; rows that evaluate to UNKNOWN or FALSE are not selected.
Column space utilization for NULL values is 1 bit per column and space is allocated in multiples of 8 bits. The NULL bit usage is fixed based on the number of columns in the table that allow NULL values.
The IS [ NOT ] truth-value clause, where truth-value is one of TRUE, FALSE or UNKNOWN can be used to select rows where the NULL value is involved.
In the following examples, the column Salary contains NULL.
Condition | Truth value | Selected? |
---|---|---|
Salary = NULL | UNKNOWN | NO |
Salary <> NULL | UNKNOWN | NO |
NOT (Salary = NULL) | UNKNOWN | NO |
NOT (Salary <> NULL) | UNKNOWN | NO |
Salary = 1000 | UNKNOWN | NO |
Salary IS NULL | TRUE | YES |
Salary IS NOT NULL | FALSE | NO |
Salary = expression IS UNKNOWN | TRUE | YES |
The same rules apply when comparing columns from two different tables. Therefore, joining two tables together does not select rows where any of the columns compared contain the NULL value.
NULL also has an interesting property when used in numeric expressions. The result of any numeric expression involving the NULL value is NULL. This means that if NULL is added to a number, the result is NULL—not a number. If you want NULL to be treated as 0, you must use the ISNULL( expression, 0 ) function.
Many common errors in formulating SQL queries are caused by the behavior of NULL. You have to be careful to avoid these problem areas.
In SQL, comparisons to NULL within search conditions yield UNKNOWN as the result. However, when determining whether or not two rows are duplicates of each other, SQL treats NULL as equivalent to NULL. These semantics apply to the set operators (UNION, INTERSECT, EXCEPT), GROUP BY, PARTITION within a WINDOW clause, and SELECT DISTINCT.
For example, if a column called redundant contained NULL for every row in a table T1, then the following statement would return a single row:
SELECT DISTINCT redundant FROM T1; |
Must be connected to the database.
None.
SQL/2008 Core feature.
Transact-SQL In some contexts, Adaptive Server Enterprise treats comparisons to NULL values differently. If an expression is compared to a variable or NULL literal using equality or inequality, and if expression is a simple expression that refers to the column of a base table or view, then the comparison is performed using two-valued logic, with NULL = NULL yielding TRUE rather than UNKNOWN. The list of possible comparisons with these semantics, and their SQL/2008 equivalents, are as follows:
Transact-SQL comparison | SQL/2008 equivalent |
---|---|
expression = NULL | expression IS NULL |
expression != NULL | NOT (expression IS NULL) |
expression = variable | expression = variable IS TRUE OR (expression IS NULL AND variable IS NULL) |
expression != variable | expression != variable IS TRUE AND ( NOT expression IS NULL OR NOT variable IS NULL) |
SQL Anywhere will implement these semantics to match Adaptive Server Enterprise behavior if the ansinull option is set to OFF. The ansinull option is set to OFF by default for Open Client and jConnect connections. To ensure SQL/2008 semantics, you can either reset the ansinull option to ON, or use an IS [NOT] NULL predicate instead of an equality comparison.
Unique indexes in SQL Anywhere can hold rows that hold NULL and are otherwise identical. Adaptive Server Enterprise does not permit such entries in unique indexes.
If you use jConnect, the tds_empty_string_is_null option controls whether empty strings are returned as NULL strings or as a string containing one blank character.
The following INSERT statement inserts a NULL into the date_returned column of the Borrowed_book table.
INSERT INTO Borrowed_book ( date_borrowed, date_returned, book ) VALUES ( CURRENT DATE, NULL, '1234' ); |
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |