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

Question

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)

It may make it easier for you to know that the sum of any set containing `NULL` is also `NULL`... — JNK, Nov 17 '10 at 15:48
I guess you'll get a wide range of answers from Oracle, to SQL Server, until you let us know what database system you are using. Greatest and least work with Oracle. My solution should work for both. — JonH, Nov 17 '10 at 16:00
This is quite late, but may be useful for anyone coming here from Google like I did. In response to @JNK, at least in SQL Server R2, NULL does not affect the SUM of a set at long as there is at least one non-NULL value in the set. For example, `SELECT SUM(val) sum_of_value FROM ( SELECT 4 val UNION ALL SELECT 5 UNION ALL SELECT NULL ) test` returns 9 for me, and not NULL. — pcronin, Jul 10 '13 at 10:51
@pcronin Sorry, by sum I meant inline addition, not `SUM()` aggregate function. `SELECT 1+1+1+NULL` — JNK, Jul 10 '13 at 10:55

Martin Schapendonk · Answer 1 · 2010-11-17T16:16:33.650

3

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

Life can be so easy :-)

(edit: works on Oracle)

edited Nov 17 '10 at 16:16

answered Nov 17 '10 at 15:52

Martin Schapendonk

12,893
3
19
24

score 2 · Accepted Answer · edited Feb 08 '11 at 19:01

2

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

edited Feb 08 '11 at 19:01

Rick Rodriguez

119
1
1
8

answered Nov 17 '10 at 19:24

Axn

356
4
7

This is the nuts and bolts of what I needed. Thank you all very much, sincerely. As a guy learning SQL from the web, it's nice to have a resource to connect myself with people who know WAAAAY more than I. I wasn't aware if there was a built-in function or if I would have to use a case statement. – Rick Rodriguez Nov 17 '10 at 19:41
This would not return the max of the 5 if all five are not null. – Martin Schapendonk Nov 18 '10 at 07:14
OP specified that he was willing to accept returning any one of them. – Axn Nov 18 '10 at 16:40
1

I appreciate all the help. I know this comment is way past due, but the real answer to this problem is in the same class as this solution provided. – Rick Rodriguez Feb 08 '11 at 18:54

score 1 · Answer 3 · answered Nov 17 '10 at 15:53

1

Try this:

SELECT
    CASE WHEN t1.SomeDate IS NULL THEN NULL ELSE MAX(t1.SomeDate) END AS TheVal
FROM
(
SELECT C1 AS SomeDate FROM Table_1
UNION ALL
SELECT C2 AS SomeDate  FROM Table_1
UNION ALL
SELECT C3 AS SomeDate FROM Table_1
UNION ALL
SELECT C4 AS SomeDate FROM Table_1
UNION ALL
SELECT C5 AS SomeDate FROM Table_1
) t1
GROUP BY
t1.SomeDate

answered Nov 17 '10 at 15:53

JonH

32,732
12
87
145

What is your output when you run the query against OP's data? Do you get two records or three? – Axn Nov 17 '10 at 19:12
I am getting 3 records, NULL, 1991 and 1992 when running your query. Are you sure you're using the right dataset? – Axn Nov 17 '10 at 20:37
OK, first comment was inaccurate, I deleted it. @JonH As you smash all columns into rows with a union all, there is no guarantee that your solution will produce 1 result per original record. Change the original data to have 9 different dates and a null. Your query then returns 10 results instead of 2. That explains my downvote on your solution. – Martin Schapendonk Nov 18 '10 at 07:22

score 0 · Answer 4 · answered Jun 20 '19 at 01:45

0

perhaps a variation on coalesce (replace -1 with an invalid value)?

SELECT CASE WHEN COALESCE(C1,C2,C3,C4,C5,-1) = -1 THEN NULL ELSE COALESCE(C1,C2,C3,C4,C5) END

answered Jun 20 '19 at 01:45

user3796717

1
2

score -1 · Answer 5 · answered Nov 17 '10 at 15:50

-1

Maybe with LEAST? I don't know how this works with NULL.

answered Nov 17 '10 at 15:50

MatTheCat

18,071
6
54
69

score -2 · Answer 6 · edited Oct 01 '13 at 07:12

-2

SELECT 
    CASE WHEN C1 IS NULL THEN C2 WHEN C1 IS NULL AND C2 IS NULL THEN C3 WHEN C1 IS NULL AND C2 IS NULL AND C3 IS NULL THEN C4 WHEN C1 IS NULL AND C2 IS NULL AND C3 IS NULL AND C4 IS NULL THEN C5 ELSE C1 END AS REQUIREDNOTNULLVALUE 
FROM 
TABLE1

edited Oct 01 '13 at 07:12

Praveen Prasannan

7,093
10
50
70

answered Nov 17 '10 at 18:08

Ashok Gupta

2,247
5
28
34

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

6 Answers6