What NULL means, how to account for it, and how are expressions involving NULL evaluated
Null values in SQL Server have long been a cause of grief for many query designers, as they realize along the way that NULL values require special handling. The main cause of confusion, I would say, is thinking that NULL means blank or empty. In this article we summarize the semantics of NULL and provide some examples of its use and its consequences for query designers.
The nullability of a column determines whether the rows in the table can contain a null value for that column. For example, in the following table-creation script, column FirstName does not allow NULLs, but LastName does:
CREATE TABLE [dbo].[Person](
[FirstName] [varchar](50) NOT NULL,
[LastName] [varchar](50) NULL
) ON [PRIMARY]
A NULL value in SQL Server is (by design) meant to indicate an unknown or undefined value, and as such, is different from any other value, including another NULL. Let’s look at some examples to see what this all means.
Any comparisons involving NULLs will have a NULL result, like these ones:
NULL <> NULL
NULL > 5
Also, any non-logical expressions involving NULLs have an unkown, or NULL, result. For example:
NULL + 4
returns a result of NULL.
AND and OR tables with NULL/UNKNOWN values
This next table summarizes the effect of NULLs in AND expressions:
For example, the result of the expression
NULL and True
is NULL. But note that
NULL and False
is False, since for an entire AND expression to result in True all of its operands must be true, and in this case we are certain that at least of them is False.
This second table summarizes the effect of NULLs in OR expressions:
For example, the result of the expression
NULL or False
is NULL. But note that
NULL or True
is True, since for an entire OR expression to result in True at least one of its operands must be true, and in this case we are certain that at least of them is True.
How to account for NULL values in expressions
If you write expressions using columns that may have NULL values, you must explicitly program for the possibility of NULLs. The way to do this is with the IS NULL and IS NOT NULL operators and with the ISNULL and COLASCE functions. For example, if you want to explicitly test whether some value is NULL, the correct way of doing it is
MyValue IS NULL
The incorrect way would be
MyValue = NULL
because, as we mentioned above, any logical expression involving NULLs results in NULL, which is not True. So, if MyValue were in fact NULL, this second expression would not be true!
The ISNULL function takes two arguments. It returns the value of the first one if it is not NULL, and the value of the second one if the first one is NULL. For example,
ISNULL('jane', 'john')
returns ‘jane’, and
ISNULL(NULL, 'john')
returns ‘john’.
Finally, the COALESCE function takes one or more parameters and returns the first one that is not null. For instance:
COALESCE('john', 'jane', NULL)
evaluates to ‘john’, and
COALESCE(NULL, 'john', 'jane')
also evaluates to ‘john’. To see the relationship between ISNULL and COALESCE, note that these two expressions are equivalent:
COALESCE(@MyVariable, 0)
ISNULL(@MyVariable, 0)
because if @MyVariable is NULL, both will evaluate to 0, and if it’s not, both will evaluate to the value of @MyVariable. As COALESCE is in the ANSI Standard, it should be the preferred over ISNULL.
A more concrete example
Let’s do a concrete example, to see the effects of all of these rules. Consider this table:
CREATE TABLE [dbo].[tbl](
[number] [int] NULL,
[string] [varchar](50) NULL,
[boolean] [bit] NULL
) ON [PRIMARY]
Note that all of the columns allow NULLs. Now we this data in the table:
Let’s say we wanted to know all of the rows in which the number column is null. Consider this query to accomplish this goal:
select * from tbl
where number = null;
Let’s think: any logical expression involving NULL results in NULL, which is not True. Thus, the WHERE clause fails to be True for every row, and our result is 0 rows. A correct query would be:
select * from tbl
where number is null;
which results in:
as expected.
As a second example, let’s say we wanted to know all of the people whose name is not ‘jane’. One would think to do it like this:
select * from tbl
where string <> 'jane';
but it returns
This result is missing the row where string is NULL, because the expression
NULL <> 'jane'
evaluates to NULL. A row if part of a query result if the where clause evaluates to True for it (note how this is different for constraints, where a row is accepted if the constraint is not False). A correct query would be:
select * from tbl
where (string is NULL or string <> 'jane');
but which could be shortened using the ISNULL function:
select * from tbl
where ISNULL(string,'') <> 'jane';
or better, COALESCE:
select * from tbl
where COALESCE(string,'') <> 'jane';
Now one last example, using the boolean column. If we wanted to know all of the rows with a True value in this column, this would be correct:
select * from tbl
where boolean = 1;
but don’t be tricked into thinking that this other query gives you all of the non-true rows:
select * from tbl
where boolean = 0;
Since the column allows NULLs, this would be a way of accomplishing this task:
select * from tbl
where COALESCE(boolean,1) = 0;
In summary: if a column allows NULL values, your queries need to account for them.
Keys and indexing
When defining table schemas and deciding whether or not to have columns that allow NULL values, you need to take into consideration the keys and indexes you will need. This list summarizes how NULL values impact key and index definitions in SQL Server 2008 and 2012:
- Nulls cannot be used in PK columns.
- Nulls can be used in FK columns. (We have quite often encountered designs where we must allow NULLs in FK columns to indicate an unknown or not-yet-established relationship.)
- Nulls can be used in unique indexes and they count as a unique value. That is, one row, but not two, may have NULL as its value for the indexed column.
- Nulls can be used in non-unique indexes.
Design considerations
One way to avoid worrying about NULLs is never to use them, always declaring columns as not allowing NULLs and designating default values for “empty” or “unknown”. This will save you keystrokes, especially when you want to check whether a column does not have a certain value. However, you will anyway probably have to write logic for when columns have the designated default values.
One last note
The handling of NULLs as we have summarized follows the ANSI standard. However, Transact-SQL offers an extension for null processing: If the option ANSI_NULLS is set to OFF, comparisons between nulls, such as NULL = NULL, evaluate to TRUE.
This article was previously published in SQLServerCentral.com.
