COALESCE

SQL COALESCE is a useful function that returns the first non-null value from a list of expressions. It takes any number of expressions as arguments, and returns the first non-null expression. If all expressions evaluate to null, then it returns null.

Syntax

The syntax of the COALESCE function is as follows:

COALESCE(expression1, expression2, expression3, ...)

Here, expression1, expression2, expression3, etc., are the expressions that are evaluated in order until the first non-null value is found.

COALESCE can be used with any data type that can be compared. For example, you can use COALESCE with integers, strings, dates, or any other data type supported by SQL.

Example

Let’s consider an example to understand how the COALESCE function works. Suppose you have a table called employees with the following data:

id	name	salary
1	John	NULL
2	Jane	50000
3	Michael	60000
4	Samantha	NULL

CREATE TABLE employees
(
id int, 
name varchar(250),
salary int
);

INSERT INTO employees(id, name, salary) 
VALUES
(1,'John',null), (2,'Jane', 50000), 
(3,'Michael', 60000), (4,'Samantha', null);

To get the first non-null value from the salary column for each employee, you can use the COALESCE function in your query, like this:

SELECT 
name, COALESCE(salary, 0) AS salary
FROM employees;

This query will return the following result:

name	salary
John	0
Jane	50000
Michael	60000
Samantha	0

In this example, the COALESCE function returns the value 0 for John and Samantha because their salary values are NULL. For the other employees, the actual salary value is returned.

In conclusion, the COALESCE function is a simple and useful tool that can make your SQL queries more efficient by eliminating the need for nested IF statements or complicated CASE expressions. By using COALESCE, you can easily handle null values in your data and ensure that your queries return the expected results.