How do I work with NULL?

NULL which can be tricky to work with and requires special treatment in a number of circumstances. In general, never assume that NULL will behave like a 0 in a numeric data field, the empty string in a CHAR field, and so on.

Summations

If a column may contain a null, always use the ISNULL function to return a 0 so that your summation queries will not crash. Here’s a quick example that you can run on the pubs database:

USE pubs
GO
SELECT SUM(ISNULL(price, 0))
FROM titles
GO

ISNULL is a T-SQL function that is often used to replace NULL values with another value. In the above example, it is used to turn all NULL values into 0. ISNULL always returns its first argument except when it is NULL, in which case it returns its second argument.

NULL and the COUNT function

Most aggregate functions eliminate null values in calculations; one exception is the COUNT function. When using the COUNT function on a column containing NULL values, the NULL values will be eliminated from the calculation. However, if the COUNT function is used with an asterisk, it will count all rows regardless of whether NULL values are present.

If you want the COUNT function to count all rows of a given column, including the null values, use the ISNULL function. The ISNULL function can replace the null value with a valid value.

In fact, the ISNULL function is very valuable for aggregate functions where NULL values affect the results in an erroneous fashion. Remember that when using an asterisk, the COUNT function will calculate all rows. The following sample codes illustrate the impact of null values in the AVG and COUNT aggregate functions:

SELECT  AVG(col1)         - NULL values are eliminated. 
AVG(ISNULL(col1,0)) A2    - With the IsNULL function, NULL is replaced with 0.
COUNT(col1) C1,           - NULL values are eliminated.
COUNT(ISNULL(col1,0)) C2, - With the IsNULL function, NULL is replaced with 0.
COUNT(*) C3               - count(*)calculates all rows, even those with NULLs           
FROM tabcount

NULL in WHERE clauses

A SELECT query returns records when the WHERE clause evaluates to TRUE. For example, consider the following query:

SELECT * FROM Orders WHERE (total < 1000 OR total >= 1000)

Here our tendency is to assume that the query will return all records, even those records that have a total field set to NULL. What happens, however, is that the SELECT won't include records for which the total field is NULL, because this value makes the entire WHERE expression evaluates to UNKNOWN.

Here's another example:

SELECT * FROM Orders WHERE total = 0

The above query returns all orders for which Total is zero, but not those for which Total is NULL. If you want to include the latter ones, you must explicitly look for NULL values:

SELECT * FROM Orders WHERE total = 0 OR total IS NULL

Carefully note the use of "total IS NULL" and not "total = NULL". NULL is not a value, but a state that a field can have which indicates the abscence of a value.

T-SQL extends the ANSI 92 standard and supports NULL also in IN clauses, so you can rewrite the above query as follows:

SELECT * FROM Orders WHERE total IN (0, NULL)

3-valued Logic

SQL uses a 3-valued logic. An expression can either have a value, have no value (NULL) or be UNKNOWN (because a NULL value was used in the expression).

Back

 

Advertisement

Free Magazine