Find all The Rows Where Any Of The Columns Is Null Or Zero Or Both
From Wiki
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
Contributed by: --SQLDenis 03:08, 31 May 2008 (GMT)
Part of SQL Server Programming Hacks
Section Handy tricks
