COALESCE() returns the first non-null value in the list. If you have a list of arguments that you are passing to the COALESCE function, the first non-null argument is the one that COALESCE will return. This function is available in many databases, but not all. Given the example below, what will the output be?

SELECT COALESCE(NULL, NULL, 'Bobby', NULL, 124.98);

Bobby is what the function returns.

Instead of a list of values inside parenthesis as above, you can use the function on a column in a table. Below, the table is Names4 and the two columns are FName and Number.

SELECT 
   FName, COALESCE(Number,0) As Number
FROM 
   Names4;

In a non-production server, you could run this script below to create a table and add some data into it for the purpose of testing the COALESCE function. In the CREATE TABLE statement below, you’ll notice that both columns have the word NULL. This means that NULLS are allowed. If NULLs were not allowed, it would be NOT NULL in the statement.

CREATE TABLE [dbo].[Names4](
	[FName] [varchar](50) NULL,
	[Number] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Names4] ([FName], [Number]) VALUES (N'Sally', 23)
GO
INSERT [dbo].[Names4] ([FName], [Number]) VALUES (N'Bob', 2)
GO
INSERT [dbo].[Names4] ([FName], [Number]) VALUES (N'Joe', NULL)
GO
INSERT [dbo].[Names4] ([FName], [Number]) VALUES (N'Linda', 83)
GO