Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Return Null if One Column is Null (Opposite of COALESCE())

Tags:

sql

null

coalesce

In advance, I would like to say thanks for the help. This is a great community and I've found many programming answers here.

I have a table with multiple columns, 5 of which contain dates or null.

I would like to write an sql query that essentially coalesces the 5 columns into 1 column, with the condition that if 1 of the five columns contains a "NULL" value, the returned value is null. Essentially the opposite of the coalesce condition of returning the first non-null, I want to return the first null. If none are null, returning the greatest of the 5 dates would be optimal, however I can settle with returning any one of the 5 dates.

    C1         C2          C3        C4        C5
    --         --          --        --        --
 1/1/1991   1/1/1991    1/1/1991  1/1/1991  2/2/1992
   NULL     1/1/1991    1/1/1991  1/1/1991  1/1/1991

Query Returns:

    C1
    --
 2/2/1992
   NULL

Thank you very much.

(Server is MSSQL2008)

like image 531
Rick Rodriguez Avatar asked Nov 17 '10 15:11

Rick Rodriguez


People also ask

How do you handle NULL with coalesce in SQL?

The SQL Coalesce and IsNull functions are used to handle NULL values. During the expression evaluation process the NULL values are replaced with the user-defined value. The SQL Coalesce function evaluates the arguments in order and always returns first non-null value from the defined argument list.

Does coalesce work with NULL?

The COALESCE function evaluates its arguments from left to right. It stops evaluating until it finds the first non-NULL argument. It means that all the remaining arguments are not evaluated at all. The COALESCE function returns NULL if all arguments are NULL .

What is the difference between coalesce () & Isnull ()?

Comparing COALESCE and ISNULL Data type determination of the resulting expression is different. ISNULL uses the data type of the first parameter, COALESCE follows the CASE expression rules and returns the data type of value with the highest precedence.

What does coalesce return if all values are NULL?

The MySQL COALESCE() function is used for returning the first non-null value in a list of expressions. If all the values in the list evaluate to NULL, then the COALESCE() function returns NULL.


2 Answers

select greatest(c1, c2, c3, c4, c5)
from table;

Life can be so easy :-)

(edit: works on Oracle)

like image 162
Martin Schapendonk Avatar answered Sep 20 '22 06:09

Martin Schapendonk


Without overthinking it:

SELECT
  CASE WHEN c1 is null or c2 is null or c3 is null or c4 is null or c5 is null
       THEN null
       ELSE c1
  END
FROM mytable

My edit is as follows:

CASE 
 WHEN (c1 >= c2 AND c1 >= c3) THEN c1
 WHEN (c2 >= c1 AND c2 >= c3) THEN c2
 WHEN (c3 >= c1 AND c3 >= c2) THEN c3
END
like image 37
Axn Avatar answered Sep 19 '22 06:09

Axn