Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Most Efficient Way to Return Y/N If One or More Columns Contain Non-Null Value?

What is the most concise way to return CHAR(1) indicator Y/N if one of n columns in a row contain a non-null value?

Performance is important, but not the primary consideration, in this case.

The straightforward way seems to be:

SELECT      CASE WHEN (C.TerminatedDate IS NULL
                       AND C.SelfClosedDate IS NULL
                       AND ...)
                 THEN 'Y'
                 ELSE 'N' END AS 'OpenInd'

  FROM      Customers C

Curious if there is a better way; aware of COALESCE():

SELECT      CASE WHEN COALESCE (C.TerminatedDate, C.SelfClosedDate, ...) IS NULL
                 THEN 'Y'
                 ELSE 'N' END AS 'OpenInd'

  FROM      Customers C

Is there a better way?

Database server is SQL Server 2008.

like image 260
iokevins Avatar asked Mar 08 '11 17:03

iokevins


People also ask

Which function returns the value of the non NULL value?

SQL COALESCE – a function that returns the first defined, i.e. non-NULL value from its argument list. Usually one or more COALESCE function arguments is the column of the table the query is addressed to. Often a subquery is also an argument for a function.

Which of the following would return the number of non NULL records in a table?

With SQL, how can you return the number of not null records in the “Persons” table? Explanation: COUNT(column_name) is used to count the number of rows of a table where column name is a column that does not allow NULL values.


2 Answers

Since no one is suggesting it, and the question asks for concise..

For the same data types, a straight COALESCE is best

coalesce(a,b,c,d) is not null

If you will be dealing with different data types, try a modified COALESCE

coalesce(LEFT(a,1),LEFT(b,1),LEFT(c,1)) is not null

Sample:

create table abc (a int, b datetime, c varchar(max), d image)
insert into abc select 1, GETDATE(), '', null
insert into abc select 1, null, '', null
insert into abc select 1, null, '', 0x123123
insert into abc select null, null, '', 0x123123
insert into abc select null, GETDATE(), '', 0x123123
insert into abc select null, null, null, null
insert into abc select 88, GETDATE()+3, null, null
insert into abc select 88, GETDATE()+3, 'gdasdf', null
insert into abc select null, null, '222', 0x123123
insert into abc select null, null, 'abcdef', 0x123123

select *, case when coalesce(LEFT(a,1),LEFT(b,1),LEFT(c,1)) is not null then 'N' else 'Y' end
from abc

If you are not using exotic types like VARCHAR(MAX) or IMAGE, then you can use SQL_VARIANT with COALESCE

create table abc (a int, b datetime, c varchar(10), d image)
insert into abc select 1, GETDATE(), '', null
insert into abc select 1, null, '', null
insert into abc select 1, null, '', 0x123123
insert into abc select null, null, '', 0x123123
insert into abc select null, GETDATE(), '', 0x123123
insert into abc select null, null, null, null
insert into abc select 88, GETDATE()+3, null, null
insert into abc select 88, GETDATE()+3, 'gdasdf', null
insert into abc select null, null, '222', 0x123123
insert into abc select null, null, 'abcdef', 0x123123

select *, case when coalesce(convert(sql_variant,a),b,c) is not null then 'N' else 'Y' end
from abc
like image 171
RichardTheKiwi Avatar answered Oct 03 '22 06:10

RichardTheKiwi


Theoretically, you should get the advantages of short-circuiting here by immediately returning when any value is not null

Select Case
        When C.TerminatedDate Is Not Null Then 'N'
        When C.SelfClosedDate Is Not Null Then 'N'
        ...
        Else 'Y'
        End

That said, I doubt there is a significant difference in performance between this solution and your above solutions (assuming you can use Coalesce).

like image 27
Thomas Avatar answered Oct 03 '22 05:10

Thomas