COALESCE is a built-in function in Microsoft SQL Server that returns the first non-null expression among its arguments. It takes one or more expressions as arguments and returns the value of the first expression that is not NULL. If all the expressions are NULL, it returns NULL.
The syntax for COALESCE function is as follows:
COALESCE(expression1, expression2, expression3, ...)
where expression1, expression2, expression3, and so on, are the expressions that are to be evaluated. The function returns the value of the first non-null expression from the list of expressions.
Here's an example of using COALESCE function in SQL Server:
SELECT COALESCE(NULL, 1, 'SQL Server', NULL, 100) as Result;
In this example, the COALESCE function is used to evaluate a list of expressions, including NULL, integer value 1, string value 'SQL Server', another NULL, and integer value 100. The function returns the value of the first non-null expression from the list, which is the integer value 1.
COALESCE function can be used in various scenarios in SQL Server, such as:
- To handle NULL values in expressions or columns, and replace them with default values.
Suppose you have a table Products that contains information about products, including their prices. Some of the products do not have a price assigned yet, and their price column is NULL. You want to retrieve the price of each product, but replace the NULL values with a default value of 10. Here's how you can do it using COALESCE:
SELECT ProductName, COALESCE(Price, 10) AS Price FROM Products
In this example, the COALESCE function is used to replace NULL values in the Price column with the default value of 10.
- To combine multiple columns into a single result, by selecting the first non-null value among them.
Suppose you have a table Employees that contains information about employees, including their first and last names. You want to retrieve the full name of each employee, but some of them do not have a last name assigned yet, and their LastName column is NULL. In this case, you can use COALESCE to combine the first and last names into a single column:
SELECT FirstName, COALESCE(LastName, '') AS FullName FROM Employees
In this example, the COALESCE function is used to combine the FirstName and LastName columns into a single column called FullName. If the LastName is NULL, it will be replaced with an empty string.
- To simplify complex expressions by reducing them to a single expression that returns the desired value.
Suppose you have a table Orders that contains information about orders, including their order date and shipping date. You want to retrieve the number of days between the order and shipping dates, but some of the shipping dates are NULL, indicating that the order has not yet been shipped. In this case, you can use COALESCE to simplify the calculation and handle the NULL values:
SELECT OrderID, DATEDIFF(day, OrderDate, COALESCE(ShippingDate, GETDATE())) AS DaysToShip FROM Orders
In this example, the COALESCE function is used to handle the NULL values in the ShippingDate column. If the ShippingDate is NULL, it will be replaced with the current date (GETDATE() function). The DATEDIFF function is then used to calculate the number of days between the order and shipping dates.