COALESCE SQL – Full Guide with Real Examples (All Databases)

COALESCE SQL: The COALESCE function looks for the first non-null value from the expression. Once the database reaches a non-null value, stop the traverse through the remaining expressions. Therefore, it is a more versatile function because it handles multiple values and also doesn’t evaluate all full expressions.

What does coalesce do in sql

COALESCE: Looking for the arguments until I find the NON-NULL value (exceptions, can be there, such as a sequence NEXTVAL COALESCE SQL  is the latest function and uses the ANSI-92 standard 

coalesce sql syntax

expr1, expr2, …, expr_n: Evaluate the expression from 1 to n until you find a non-null expression.

-- 
Syntax: 
COALESCE (expr1, expr2, ..., expr_n) 
SELECT COALESCE(NULL, 'A', 'B') AS result FROM dual; 
-- Output: 'A' 
--

COALESCE SQL example

COALESCE is helpful to use different kinds of functions rather than just checking or replacing null values. Here are a few simple examples that are used in different kinds of places in a query. If this is used in a WHERE condition in a query, then it sometimes misses an index because of use direct column name

Simple Replacement

--
Using COALESCE:
SELECT COALESCE(column_name, 'Default') FROM sample_table; 
--

Handling Multiple Columns

COALESCE is more versatile when there are more than 2 columns in the arguments.

-- Using COALESCE 
SELECT COALESCE(column1, column2, 'Default') FROM sample_table; 
--

Join Conditions

COALESCE SQL is able to use join tables. However, my concern is that there may be a performance drop if there is an index on join columns, as those may not be fetched. It’s because we do not use columns as it is. Therefore, we have to use a function-based index in those types of situations. Then the database creates an index with a function like COALESCE

-- Using COALESCE 
SELECT * FROM table1 
JOIN table2 ON table1.column = COALESCE(table2.column, 'Default'); 
--

Handling Numeric Values

COALESCE can be used to set default values when the numeric column value returns null.

-- Using COALESCE 
SELECT COALESCE(numeric_column, 0) FROM sample_table; 
--

COALESCE with Calculations

This example COALESCE SQL is used to set 0 if the calculated amount is null. Or there can be a null return by AVG SUM

--
SELECT 
    COALESCE(amount, 0) + price AS final_price
FROM orders;
--

How to use coalesce sql in Databases

COALESCE() is a standard SQL function that can all major databases. Below is a simple summary of COALESCE in different databases.

coalesce vs isnull

Both COALESCE and ISNULL do the same thing, which is to handle NULL values, but they’re not equally same.

Basic Example

This is doing a similar thing as we discussed earlier. ISNULL is SQL Server-specific function that cannot able to used in other database languages.  Both handle NULL values, but COALESCE  SQL handles multiple arguments.

-- Using COALESCE
SELECT COALESCE(NULL, NULL, 'My', 'World') AS Result;
-- Output: 'My'

-- Using ISNULL
SELECT ISNULL(NULL, 'My') AS Result;
-- Output: 'My'

coalesce vs nvl

As discussed, both COALESCE and NVL are used to handle NULL values, but they differ in syntax and compatibility.

NVL:
NVL(expr1, expr2)
-- Using COALESCE
SELECT COALESCE(NULL, NULL, My, 'World') AS Result FROM dual;
-- Output: My

-- Using NVL
SELECT NVL(NULL, ‘My’) AS Result FROM dual;
-- Output: My

coalesce sql in Spark SQL

COALESCE Function in Spark SQL (for NULL handling)

Just like standard SQL, COALESCE(expr1, expr2, …) returns the first non-NULL value.

Mysql coalesce

In MySQL, the COALESCE() function returns the first non-NULL value from a list of parameters.

As you know, this is SQL standard, so it works in MySQL, Oracle, PostgreSQL, SQL Server, and others. IFNULL() is MySQL-specific, which handles just one NULL.

Oracle COALESCE vs NVL Performance

Problem: I have a student table and a fees column. I would like to get the class fees from the fees column.

So I have to check whether fees are available in the fees column otherwise, I have to calculate the amount in real-time and show it in the output result. I hope to add 10000 students to my table and add an intentionally null value for some students.

NVL Example Code
-- NVL
SELECT  student_id, student_name,  (SELECT  SUM(val)
FROM    (
        SELECT  NVL(fees, LENGTH(RAWTOHEX(SYS_GUID()))) AS val
        FROM    dual
        CONNECT BY
                level <= 10000
        )) as fees
FROM  Students  
--
COALESCE Example Code
-- COALESCE
SELECT  student_id, student_name, (SELECT  SUM(val)
FROM    (
        SELECT  COALESCE(fees, LENGTH(RAWTOHEX(SYS_GUID()))) AS val
        FROM    dual
        CONNECT BY
                level <= 10000
        )) as fees
FROM  Students       
--

NVL Code took a lot of time to fetch records from the database, but the COALESCE SQL query fetched records in considerably less time compared to NVL. It is because NVL and COALESCE have separate running mechanisms, and my thinking is this kind of scenario, COALESCE performs well.

This is one good example of how to change Oracle COALESCE vs NVL performance. Therefore, use NVL or Oracle COALESCE according to the situation. Because both are good if you use them in the correct place.

Now you can decide on COALESCE to do everything that NVL can do. Is it? But in some cases better to use NVL rather than COALESCE 

• NVL – The huge benefit of NVL is no need to add both parameters of the same type.
• COALESCE – Expect all the expressions in the same data type therefore fore you have to add all the expressions in the same data type.

Complete COALESCE vs NVL Performance Example>

Reference

https://stackoverflow.com/questions/950084/oracle-differences-between-nvl-and-coalesce#:~:text=NVL%20and%20COALESCE%20are%20used,of%20a%20non%2DNull%20value.