Understanding the MS-SQL Server ISNULL() Function

You can use the MS-SQL Server ISNULL() built-in function to return a value other than NULL for columns that are NULL. Suppose, for example, that your EMPLOYEE table has data in columns as shown in Figure 31.1.

EMPLOYEE table
ID	NAME	DATE_HIRED	QUOTA
1	Sally Smith	04/27/00	NULL
2	Wally Wells	04/13/99	5
3	Greg Jones	05/12/97	7
4	Bruce Williams	04/15/00	NULL
5	Paul Harvey	06/05/99	9

Figure 31.1: EMPLOYEE table with sample data and NULL values

If you execute the SQL SELECT statement

 SELECT id, name, date_hired, quota FROM employee

MS-SQL Server will display output similar to the following:

 id  name            date_hired         quota
-----------------------------------------------
1   Sally Smith     04/27/00 00:00:00  NULL
2   Wally Wells     04/13/99 00:00:00  5
3   Greg Jones      05/12/97 00:00:00  7
4   Bruce Williams  04/15/00 00:00:00  NULL
5   Paul Harvey     06/05/99 00:00:00  9

If you don't want to explain what a NULL is to your users, you can use the built-in ISNULL() to replace "(null)" in the output with another text string or number.

The syntax of the ISNULL() function is:

ISNULL(expression,value)

Substitute the name of the column that contains NULLs for expression and the character string or number you want displayed in place of "(null)" for value. Therefore, if you want MS-SQL Server to replace "(null)" in the QUOTA column with "In Training," use the SQL statement

SELECT
 id, name, date_hired, 'quota'=ISNULL(quota,'In Training')
FROM
 employee

to have MS-SQL Server output the following for our example data:

 id  name            date_hired         quota
------------------------------------------------
1   Sally Smith     04/27/00 00:00:00  In Training
2   Wally Wells     04/13/99 00:00:00  5
3   Greg Jones      05/12/97 00:00:00  7
4   Bruce Williams  04/15/00 00:00:00  In Training
5   Paul Harvey     06/05/99 00:00:00  9

You can also use the MS-SQL Server ISNULL() function to select either rows where a column is NULL or rows where a column is not NULL. For example, if you want to see the rows in the EMPLOYEE table where the quota is null, you could use an SQL SELECT statement similar to:

 SELECT id, name, date_hired, ISNULL(quota,'In Training')
FROM   employee
WHERE  ISNULL(quota,-999) = -999

If, on the other hand, you want to see only those reps who have a defined quota, replace the = in the WHERE clause with <>, similar to the following:

SELECT id, name, date_hired, quota
FROM   employee
WHERE  ISNULL(quota,-999) <> -999