T-SQL COALESCE - Replacing NULL values - T-SQL (Transact SQL) Tutorials

SQL Bookstore
Home - Introduction
BASIC
Tables, Columns, Rows
SELECT Statement
INSERT Statement
UPDATE Statement
DELETE Statement
WHERE Clause
TRUNCATE Statement
GROUP BY
HAVING
Aggregation Functions
DISTINCT
INTERMEDIATE
T-SQL Operators
T-SQL Variables
T-SQL Data Types
CREATE TABLE
CREATE PROCEDURE
CREATE FUNCTION
CREATE INDEX
SQL JOIN
SQL OUTER JOIN
What is an index?
CAST / CONVERT
COALESCE
Aliases
CASE .. WHEN
Working with Dates
Working with Strings
ADVANCED
Cursors
WHILE Loop
TOP
PIVOT
UNPIVOT
SQL_VARIANT
TRY CATCH Blocks
WITH
XML
UNION
INTERSECT
EXCEPT
Other Functions
Partners
Document SSIS Packages
SSIS Tutorials
Information
Privacy Policy

Introduction

Often when writing queries you may want to check for NULL values and replace them with a non-NULL value. The COALESCE function is a good canidate to use for this purpose. It allows you to check if a value is NULL and then replace that value with a static value, or with another column(s). The following are two example syntaxes of using the COALESCE Function:

SELECT COALESCE([column1],'String') FROM table

SELECT COALESCE([column1],[column2],'String') FROM table

Examples

An example of using the COALESCE function would be to check a column for a NULL value and replace that NULL value with a value. In this example we can check a field called 'LastUpdateDate', which contains the last time a table record has been updated. If the record has never been updated the column will contain a NULL value. Using COALESCE we can replace the value with a default date
SELECT COALESCE([LastUpdateDate],'1/1/1900') AS [LastUpdateDate]
FROM UserTable

Another example of using the COALESCE function would be to check two related columns and use the first available value (NON-NULL value) from those columns to populate a new column. This time we will use the same 'LastUpdatedDate' column, but we will also use another column in the table called 'CreateDate', which contains the value that the record in the table was created. For this example we will assume that the CreateDate will always be populated. So for our query we will use the LastUpdateDate, unless it's value is NULL, otherwise we will use the CreateDate.
SELECT COALESCE([LastUpdateDate],[CreateDate]) AS [LastUpdateDate]
FROM UserTable

A final example of using the COALESCE function would be to check multiple related columns and use the first available value (NON-NULL value) from those columns to populate a new column. This time we will use the same 'LastUpdatedDate', and 'CreateDate' column, but this time we will assume that the 'CreateDate' column can also contain a NULL value. So for our query we will use the LastUpdateDate, unless it's value is NULL, otherwise we will use the CreateDate. If the CreateDate is NULL, then we will use a default date
SELECT COALESCE([LastUpdateDate],[CreateDate],'1/1/1900') AS [LastUpdateDate]
FROM UserTable