SQL Server NULL

Learning Objective

The objective of this SQL Server tutorial is to teach you how to evaluate a value and determine whether it is NULL.

What is NULL in SQL Server?

The NULL value in SQL represents the absence of value. It is different from zero (0) or empty space (‘’). It simply means that there is no value for a field or column in a record. Since a NULL value signifies nothing it cannot be evaluated using the normal logical operators (i.e. =, !=, >, <). This is because NULL does not equal anything nor is it greater or less than something. Hence when evaluated using any of the logical operators it evaluates to UNKNOWN.

To address this issue SQL has the IS NULL operator which evaluates a value to determine whether it is NULL or not. If it is then the IS NULL expression returns TRUE otherwise FALSE. There is also an IS NOT NULL operator which performs just the opposite and returns TRUE when a value is not NULL and returns FALSE when it is.

Operation

The IS NULL operator is used in a logical expression with the WHERE clause when we need to evaluate and determine whether a column value is NULL or not. The converse logic applies for the IS NOT NULL operator. It can be used with SELECT, UPDATE and DELETE.

Syntax

The basic syntax of IS NULL (and IS NOT NULL) is given below.

WHERE expression IS NULL;
WHERE expression IS NOT NULL;

In this syntax,

• expression – something that returns a unique value (can be a literal value itself or a column, parameter, variable, subquery, mathematical operation, function etc.).
• IS NULL – operator used to check and determine whether a value is NULL.
• IS NOT NULL– operator used to check and determine that a value is not NULL (i.e. a valid value).

SQL Server IS NULL Examples

To understand its practical usage let us consider the following sample customers table which contains customer information. It consists of a number of columns or fields some of which are optional and contains NULL values for some records.

id	name	sex	city	phone
1	Stephen Fleming	M	Philadelphia	16107575525
5	John Wells	M	New York	NULL
7	Alice Rossi	F	NULL	NULL
8	Jack Crow	M	Los Angeles	16616165325
9	Laura Smith	F	NULL	16633775159
10	Vicky Williams	F	NULL	NULL
11	David Mathews	M	Washington	16277212992
12	Richard Anderson	M	Washington	16277212992
13	Jason Kinght	M	NULL	NULL
14	Patricia Christ	F	Philadelphia	NULL

1)  SQL Server NULL– IS NULL example

This is where the IS NULL operator comes into play. Using the same we can determine those records where the customer city is not available using the following query.

SELECT *
FROM customers
WHERE city IS NULL;

 It will generate the following output where the value for the column city is NULL (or not known).

id	name	sex	city	phone
7	Alice Rossi	F	NULL	NULL
9	Laura Smith	F	NULL	16633775159
10	Vicky Williams	F	NULL	NULL
13	Jason Kinght	M	NULL	NULL

We can also check for both city and phone at the same (i.e. check records with NULL values for all the optional columns in a table) with the following query combining multiple IS NULL expressions.

SELECT *
FROM customers
WHERE city IS NULL
AND phone IS NULL;

It will generate the following output where the value for both columns is NULL.

id	name	sex	city	phone
7	Alice Rossi	F	NULL	NULL
10	Vicky Williams	F	NULL	NULL
13	Jason Kinght	M	NULL	NULL

2)  SQL Server NULL– IS NOT NULL example

Now let us see an example using the IS NOT NULL operator to fetch those values from the table where the phone numbers are NOT NULL (i.e. where the customer phone numbers are available). The below query does the same.

SELECT *
FROM customers
WHERE city IS NOT NULL;

id	name	sex	city	phone
1	Stephen Fleming	M	Philadelphia	16107575525
8	Jack Crow	M	Los Angeles	16616165325
9	Laura Smith	F	NULL	16633775159
11	David Mathews	M	Washington	16277212992