CODEWITHSUNDEEP
sql-serverisnull
mediasurface
mediasurface

Reputation: 47

ISNULL twice for the same column

Is it possible to use ISNULL twice for the same column?

ISNULL(ISNULL(column, SELECT sum(column2) FROM table WHERE type = '1')), SELECT sum(column2) FROM table WHERE type = '2'))

Or should I be doing this in a different way with IF ELSE somehow? How would that look like?

Upvotes: 2

Views: 2465

Answers (3)

spencer7593
spencer7593

Reputation: 108380

Yes, it is possible.

(The issue I see with the expression in the question is unbalanced parens; two extra closing parens)

The ISNULL function takes two arguments. Either (or both) of those arguments can be expressions, and an ISNULL function is an expression. So yes, you can nest ISNULL functions two, three, four, or more levels deep as you need.

A SELECT statement that returns a single row containing a single column can (usually) be used as an expression. And multiple SELECT statements (subqueries) within a query can reference the same table(s) and same column(s).

In many cases, when we see a query of this type, it points either to a model that isn't working well, or it points to looking at a different way of getting an equivalent result.

EDIT

As other answers have pointed out, the more portable, ANSI-standard compliant COALESCE function can be used to return an equivalent result.

These three expressions are equivalent:

ISNULL(ISNULL(a,b),c) 
ISNULL(a,ISNULL(b,c)) 
COALESCE(a,b,c)

which are also equivalent to these (unnecessarily redundant) expressions:

COALESCE(COALESCE(a,b),c)
COALESCE(a,(COALESCE(b,c))

Test case:

create table tst (id int, a int, b int, c int);
insert into tst values 
(1,NULL,NULL,NULL)
,(2,21,NULL,NULL)
,(3,NULL,32,NULL)
,(4,NULL,NULL,43)
,(5,51,52,NULL)
,(6,61,NULL,63)
,(7,NULL,72,73)
,(8,81,82,83);

-- SQL Server
SELECT ISNULL(ISNULL(a,b),c) AS t1 
     , ISNULL(a,ISNULL(b,c)) AS t2
     , COALESCE(a,b,c)       AS t3
  FROM tst
 ORDER BY id

-- MySQL
SELECT IFNULL(IFNULL(a,b),c) AS t1 
     , IFNULL(a,IFNULL(b,c)) AS t2
     , COALESCE(a,b,c)       AS t3
  FROM tst
 ORDER BY id

-- Oracle
SELECT NVL(NVL(a,b),c)       AS t1 
     , NVL(a,NVL(b,c))       AS t2
     , COALESCE(a,b,c)       AS t3
  FROM tst
 ORDER BY id

Upvotes: 1

John Tabernik
John Tabernik

Reputation: 342

Try using COALESCE (if you are using SQL Server). http://msdn.microsoft.com/en-us/library/ms190349.aspx

It will give you the first non-null value from a list of values. Do you need more help structuring the SQL also?

Upvotes: 0

user47589
user47589

Reputation:

Look at the coalesce operator. Your query then becomes:

COALESCE(column, 
    (SELECT sum(column2) FROM table WHERE type = '1'), 
    (SELECT sum(column2) FROM table WHERE type = '2'))

It returns the first non-null result from its arguments.

Upvotes: 3

Related Questions