This page has 0 threads | Add post

Dealing with nulls - ISNULL, COALESCE and CASE

Written by Andy Brown

In this tutorial

The ISNULL Function
The COALESCE Function
Processing nulls using CASE WHEN

This part of this tutorial looks at the thorny issue of nulls. You can get one of these in a table by pressing CTRL + 0, although usually they're already there!

In this table of people, we've removed one person's first name.

There are 3 possible ways to deal with nulls in expressions: using IsNull, Coalesce or CASE. I've explained these under separate headings below!

The ISNULL Function

This function substitutes a given value when a column is null. The syntax is:

IsNull(Expression which may be null, what to use instead)

Here's an example, showing someone's first name for the table above:

-- show people's names

SELECT

IsNull(FirstName, 'Not given') AS 'First name',

LastName

FROM

tblPerson

Here's what this would give for our table above:

SQL has substituted the words Not given when the first name is null.

The COALESCE Function

This strangely-named function allows you to try multiple values. The syntax is:

=COALESCE(First value which may be null, second value which may be null, ... , last value to try)

Here's an example, returning someone's phone number by trying various columns in turn:

-- get valid phone number

COALESCE(

MobileNumber,

WorkPhone,

HomePhone,

'No phone number given'

) AS Phone

You can always use COALESCE instead of ISNULL, by just including two arguments for it.

Processing nulls using CASE WHEN

This is my personal favourite, since it builds on something with which every SQL programmer should be familiar - the CASE statement (see previous part of this blog). We could show the first name for the example at the start of this page as follows:

SELECT

FirstName,

-- show first name without null

CASE

WHEN FirstName is null THEN 'Not given'

ELSE FirstName

END AS 'First name',

LastName

FROM

tblPerson

This would give the same results:

The second column gives the first names of people, but with null values removed.

Previous
tutorial

WHERE criteria (version B)

Previous
part

CASE WHEN statement

Top of page

Next
part

Computed Columns in SQL Server

Next
tutorial

Dates and times

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on SQL

Calculations and Expressions

Dealing with nulls - ISNULL, COALESCE and CASE

The ISNULL Function

The COALESCE Function

Processing nulls using CASE WHEN

Head office

London

Manchester