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.
