COALESCE Function in TSQL

If you are new to TSQL or SQL in general, it’s always good idea to read about functions which we rarely use. Someday it might come handy (Who know it’s a tricky question and people want to trip you down memory lane.)

So let’s try to understand COALESCE function.

If you are not a big fan of msdn site, I don't blame you!!!! However it will give you a great starting point to do your research.

According to MSDN (http://msdn.microsoft.com/en-us/library/ms190349.aspx)

COALESCE says that it will "Returns the first nonnull expression among its arguments." 

Let’s go through some example:

Suppose we have a table called Address with three columns (Home Number, Work Number and Cell Number) along with all other columns. Also suppose that none of these column (phone number columns) are NOT NULL (meaning it’s up to user to enter information if he or she likes it). So we can have some users who entered phone number under Home and some under Work and still few under cell number and same never bothered to enter any number.

Let’s assume that we want to see this information in our select output along with other information.  Our preference choice is Home number first, Work number second and cell number third or it can be cell number first, home number second and work number third. Also we just want to show only 1 number at any given time. So how do we do this in our TSQL.

Here’s come-à COALESCE

Let’s go with our first choice (home then work then cell)

Select COALESCE (Home Number, Work Number, Cell Number) AS Phone Number from Address

This statement will look at home number first, if there is home number, it will return that number. If home number is missing, it will jump to work number. If work number is present it will return as output otherwise it will jump to cell number. So our result will have one number from these three columns.

COALESCE (444-444-4444, 555-555-5555, 666-666-6666) will give you-à 444-444-4444

COALESCE (NULL, 555-555-5555, 666-666-6666) will give you à 555-555-5555

COALESCE (NULL, NULL, 666-666-6666) will give you à 666-666-6666

More generic example:

COALESCE(1, 2, 3, 4, 5, NULL)à 1

COALESCE(NULL, NULL, NULL, 1, 2, 3) à 1

COALESCE(9, 8, 7, 6, 5, NULL)à 9

COALESCE(NULL, NULL, NULL, 4, 5, NULL)  à 4

What happen if none of the three columns have any number? In that case it will return a NULL value.

COALESCE(NULL, NULL, NULL, NULL) à NULL