COALESCE vs. ISNULL
Learn about the important differences
between these two T-SQL functions
The COALESCE and ISNULL T-SQL functions are used to return the first nonnull expression among the input arguments. SQL Server practitioners often wonder what the difference is between
the two functions. Several differences exist; some are straightforward
and are common knowledge, whereas others are less straightforward
and are less well-known.
Some of the examples that I employ in this article use a sample
database called TSQL2012. Download the code to create and populate
the sample database.
I’d like to thank Brad Schulz, Erland Sommarskog, Paul White, and
Umachandar Jayachandran (UC), who were the source of and inspiration for some of the information covered in this article.
Itzik
Ben-Gan
is a cofounder of SolidQ. He’s a
SQL Server MVP and is the
author of several books about
T-SQL, including Microsoft SQL
Server 2012 High-Performance
T-SQL Using Window Functions
(Microsoft Press).
COALESCE and ISNULL
According to SQL Server Books Online, COALESCE “returns the first
nonnull expression among its arguments,” and ISNULL “replaces
NULL with the specified replacement value.” As a simple example,
the following code demonstrates using the two functions:
Email
Twitter
Website
SET NOCOUNT ON;
USE TSQL2012; -- this database is used in later examples
Blog
DECLARE
@x AS INT = NULL,
@y AS INT = 1759,
@z AS INT = 42;
Download
Download the code
