THE SQL Server Blog Spot on the Web
Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search
Home Blogs Downloads   Opml View as RSS news feed in XML

Denis Gobo

Tip: Find all The Rows Where Any Of The Columns Is Null Or Zero Or Both


This question is asked every now and then so I decided to do a little blog post. How can you quickly without writing a bunch of OR statements determince if any columns have a NULL value, a value of 0 or if the value is 0 or NULL?
To test for NULL is very easy, you just concatenate the columns since NULL + anything else is always NULL. Okay that also depends on a setting.

Run this

SET CONCAT_NULL_YIELDS_NULL ON
SELECT NULL + '1' --NULL

SET CONCAT_NULL_YIELDS_NULL OFF
SELECT NULL + '1' --1

As you can see if CONCAT_NULL_YIELDS_NULL is OFF then the result is 1

Now take a look at this


SET CONCAT_NULL_YIELDS_NULL ON
SELECT NULL + 1 --NULL

 

SET CONCAT_NULL_YIELDS_NULL OFF
SELECT NULL + 1 --NULL


So with numeric values it behaves differently. Either way by default CONCAT_NULL_YIELDS_NULL is set to on
To test for NULLS or zeroes you use NULLIF
To test for zeros you can combine COALESCE and NULLIF

Here is the code which shows all of that

CREATE TABLE #test(column1 int,column2 varchar(4),column3 float)

INSERT #test VALUES(2,'2',2)
INSERT #test VALUES(0,'1',0)
INSERT #test VALUES(null,'1',0)
INSERT #test VALUES(1,null,0)
INSERT #test VALUES(0,'1',null)
INSERT #test VALUES(null,null,null)



--Any column is Null
SELECT * FROM #test
WHERE column1 + column2+column3 is null

Output
NULL 1 0.0
1 NULL 0.0
0 1 NULL
NULL NULL NULL

 

--Any column is Null or zero
SELECT * FROM #test
WHERE NULLIF(column1,0) + NULLIF(column2,0)+NULLIF(column3,0) is null

Output
-------------------
0 1 0.0
NULL 1 0.0
1 NULL 0.0
0 1 NULL
NULL NULL NULL


 

--Any column is zero
SELECT * FROM #test
WHERE NULLIF(COALESCE(column1,1),0) +
NULLIF(COALESCE(column2,1),0)+
NULLIF(COALESCE(column3,1),0) is null

Output
-------------------
0 1 0.0
NULL 1 0.0
1 NULL 0.0
0 1 NULL

 
DROP TABLE #test
Published Friday, January 25, 2008 4:22 PM by Denis Gobo
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Peter W. DeBetta said:

Cardinality of NULL and 0 play a big role in the query design. In general, it is bad to wrap columns in functions or expressions that disallow the use of an index. If the cardinality of NULL or 0 is low, then these queries you present are not good choices because they will always do a table scan whereas other query designs would allow the optimizer to make better choices (individual indexes and merge joins, for example). I confirmed this by testing this scenario using a largeer table (1 million rows) with the three columns having sparse data values of NULL or 0 (27 rows total), the following queries were the same or better because it allowed the optimizer to make better choices.

--Any Column is null

SELECT * FROM LargeAndNull WHERE column1 IS NULL OR column2 IS NULL OR column3 IS NULL

--Any column is null or 0

SELECT * FROM LargeAndNull WHERE column1 IS NULL OR column2 IS NULL OR column3 IS NULL OR column1 = 0 OR column2 = 0 OR column3 = 0

--Any column is 0

SELECT * FROM LargeAndNull WHERE column1 = 0 OR column2 = 0 OR column3 = 0

January 25, 2008 4:33 PM
 

Denis Gobo said:

Peter, I gave a very simple example. I responded to the following question

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2743021&SiteID=1

The person wanted to check ALL the columns. I was too lazy to create a table with 20 columns in my example and I would not expect a person to have indexed all of these 20 columns either. But yes you are right that a function on a column will force a scan instead of a seek

Maybe I should add a disclaimer to the post ?

January 25, 2008 5:42 PM
 

Peter W. DeBetta said:

Thanks for clarifying. That certainly does change things a bit.

January 25, 2008 5:46 PM
 

David McKinney said:

Another more concise version which only works for zeros (i.e. not for nulls) turns around the usual use of IN.

SELECT * FROM #test where 0 in (column1,column2,column3)

January 28, 2008 3:06 AM
 

Vadivel said:

Denis, I guess it would work as long as you are having 'numeric' value within your varchar fields.

i.e., In your table schema, column2 is varchar(4). If you insert a record like this .. I guess your code will break.

Insert #test VALUES(2,'abc',2)

January 28, 2008 7:53 AM
 

Denis Gobo said:

Vadivel,

yep in that case you have to do

WHERE convert(varchar(30),column1) + column2+convert(varchar(30),column3) is null

which defeats the whole purpose of course  ;-()

However if the table has varchar columns you won't have this problem, for example:

CREATE TABLE #test(column1 varchar(30),column2 varchar(4),column3 varchar(30))

INSERT #test VALUES(2,'2',2)

INSERT #test VALUES(0,'1',0)

INSERT #test VALUES(null,'1',0)

INSERT #test VALUES(1,null,0)

INSERT #test VALUES(0,'1',null)

INSERT #test VALUES(null,null,null)

Insert #test VALUES(2,'abc',2)

--Any column is Null

SELECT * FROM #test

WHERE column1 + column2+column3 is null

Output

---------------------------

NULL 1 0.0

1 NULL 0.0

0 1 NULL

NULL NULL NULL

--Any column is Null or zero

SELECT * FROM #test

WHERE NULLIF(column1,'0') + NULLIF(column2,'0')+NULLIF(column3,'0') is null

Output

-------------------

0 1 0.0

NULL 1 0.0

1 NULL 0.0

0 1 NULL

NULL NULL NULL

--Any column is zero

SELECT * FROM #test

WHERE NULLIF(COALESCE(column1,'1'),'0') +

NULLIF(COALESCE(column2,'1'),'0')+

NULLIF(COALESCE(column3,'1'),'0') is null

Output

-------------------

0 1 0.0

NULL 1 0.0

1 NULL 0.0

0 1 NULL

January 28, 2008 8:53 AM
 

Vadivel said:

Yep thats true :)

January 28, 2008 11:26 AM
 

david wei said:

Create a computed column based on the expression you are using, then create index on this computed column, you will benifit from index seek. The beauty of this is you even don't need change your code, SQL server will do an auto match if you have computed column defined.

January 29, 2008 3:32 PM
 

loop said:

is there any way to check all columns, and use only one command...

sth like this

"...WHERE column1=foo OR column2=foo OR column3=foo"

replace with

"...WHERE any column=foo"

January 18, 2009 4:59 AM
 

heena said:

what for if we need to find rows where any 2 columns out of 5 are null?

pls help...

April 19, 2011 2:47 AM
 

Ashif said:

hai,friends i have one doubt in sql here i mentioned that please help me.

COLUMN NAME1    COLUMN NAME2

A                  1

A                  2

A                  NULL

B                  3

C                  NULL

this is my table from that i have to select that not null values of A and B and also select NULL value of C.that is if A having NULL values only and C having NOT NULL values only means I have to select NULL values of A and not null values of C.that is if A having both NULL and NOT NULL values means i want NOT NULL only of A and C having NULL values means i want NULL values of C.at the same time if C having NULL AND NOT NULL value means i want the NOT NULL values of C.please help me soon.am waiting

November 17, 2012 1:25 AM
 

Ashif said:

please send me the answer soon friends,this is my email address

ashif90@gmail.com

November 17, 2012 1:57 AM
 

Anu said:

Very nice !

January 14, 2013 1:49 AM

Leave a Comment

(required) 
(required) 
Submit

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog

Syndication
©2006-2012 SQLblog.comTM
Brought to you by Adam Machanic & Peter DeBetta
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement