How to get all rows of a table having NULL in all columns?

Question

How to get all rows of a table having NULL in all columns?

bharat sethi

SSCommitted

Points: 1831
More actions
March 30, 2012 at 11:56 pm

#391915

Dear All,
I am looking for a solution without compromising on Performance & without writing all column names to get all rows those have NULL or BLANK in their all columns.
For example,
We have a table named "TEST" with 4 columns (Col1, Col2, Col3, Col4) with following data -
1, Test, Exam, Online
NULL, NULL, NULL, NULL
2, Course, Practice, Improvement
NULL, NULL, NULL, NULL
NULL, NULL, NULL, NULL
NULL, NULL, NULL, NULL
Now if I need to extract 2nd, 4th, 5th and 6th rows then as per me I have to write
Select * from TEST where
((Col1 is null) and (Col2 is null) and (Col3 is null) and (Col4 is null))
OR if table has some rows with all columns = '' (BLANK) and some rows with all columns = NULL then
Select * from TEST where
((Col1 is null or Col1 = '') and (Col2 is null or Col2 = '') and (Col3 is null or Col3 = '') and (Col4 is null or Col4 = ''))
Is there any smarter way to achieve the same???

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

CapnHector SSCoach Points: 16143 More actions · Answer 1

this kinda sounds like homework. since you want all rows where its all null you have to use the queries you provided.

now lets move to the total idiocy of having a row that is all NULL's. it has no data at all. there are some reasons to store NULL's but a table that has all NULL's in every single column of a row by definition has no primary key and is just bad db design.

now all of the above is some what not applicable to views but then this is a question to those more advanced than me (mainly because im at the house unable to sleep right now so i cant test this), would a view return a row of all nulls or would that row get "left out" of the result.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Jeff Moden SSC Guru Points: 1004191 More actions · Answer 2

capn.hector (3/31/2012)
this kinda sounds like homework. since you want all rows where its all null you have to use the queries you provided.
now lets move to the total idiocy of having a row that is all NULL's. it has no data at all. there are some reasons to store NULL's but a table that has all NULL's in every single column of a row by definition has no primary key and is just bad db design.

Or maybe it's someone who has discovered the idiocy of such columns (actually, rows from the OP description) in a database and is trying to identify them all so they can be eliminated. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004191 More actions · Answer 3

bharat sethi (3/30/2012)
I am looking for a solution without compromising on Performance & without writing all column names to get all rows those have NULL or BLANK in their all columns.

You'll need to include all of the columns one way or another. You could write some SQL to build it all dynamically by interrogating INFORMATION_SCHEMA or the system tables.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004191 More actions · Answer 4

[font="Arial Black"]{EDIT} Forget the method below. I was mistaken and it doesn't work. [/font]~~Dang... I forgot about a very old trick for this because I haven't had to use it for years. The following code will demonstrate...~~--===== Create and populate a test table.

-- This isn't a part of the solution

CREATE TABLE #SomeTable

(Col1 VARCHAR(20), Col2 VARCHAR(20), Col3 VARCHAR(20), Col4 VARCHAR(20))

INSERT INTO #SomeTable

(Col1, Col2, Col3, Col4)

SELECT '1','Test','Exam','Online' UNION ALL

SELECT NULL,NULL,NULL,NULL UNION ALL

SELECT '2','Course','Practice','Improvement' UNION ALL

SELECT NULL,NULL,NULL,NULL UNION ALL

SELECT NULL,NULL,NULL,NULL

--===== Find all rows with nothing but nulls.

-- (Uncomment the WHERE clause when you're convinced)

SELECT *, CHECKSUM(*)

FROM #SomeTable

--WHERE CHECKSUM(*) = -2147481464

~~It's not practical for much except for maybe doing deletes of the totally NULL rows.~~

And, now that I think about it, I think the only reason why anyone would want to actually know this is if it were an inteview question or a test question to see if you really DID read about CHECKSUM. 😉 I've not seen such a thing happen in real life but I supposed it's possible.

~~Just keep in mind that this won't work if certain datatypes are present.~~

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398821 More actions · Answer 5

CELKO (3/31/2012)
This is impossible because the key columns cannot be NULLs.

Exactly what I was thinking. Even if you could, and assuming you have a key constraint in place, you can't, but if you could, you'd make the key values go NULL and then you can no longer distinguish one row from another.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004191 More actions · Answer 6

CELKO (3/31/2012)
This is impossible because the key columns cannot be NULLs.

Agreed. That's why I initially forgot about the technique. The entire row must be blank for the CHECKSUM(*) method to work.

Still, it looks like the OP may actually have such conditions.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TomThomson SSC Guru Points: 104773 More actions · Answer 7

Jeff Moden (3/31/2012)
Dang... I forgot about a very old trick for this because I haven't had to use it for years. The following code will demonstrate...
<snip>
Just keep in mind that this won't work if certain datatypes are present.

Not just if certain data types are present. You can end up finding rows that aren't all nulls, even rows without any nulls in them. The trouble is that CHECKSUM delivers an int, so there are only about 4 billion possible values, wheras a table with a few columns is likely to have billions of billions of possible rows so that many different rows map to each checksum value.

Heres a trivial example:

create table #t (A int, B int, C int)

insert #t values(NULL,NULL,NULL),(NULL,NULL,2127483647)

select *,CHECKSUM(*) as CKSUM from #t

drop table #t

both rows have the same checksum. If there were a few tens of millions of rows and a few hundred thousand delivering the same checksum as all nulls it would be a pretty error-prone task to search through all those to make sure that there weren't any that were not all null.

Using hashbytes will produce false hits less often (because the result is longer than 32 bits) but is still not safe.

Of course I'm inclined to believe that any table schema that doesn't have at least one candidate key is a disaster, but SQL allows it. Eliminating all-null rows and eliminating duplicate rows are an inevitable consequence of SQL's bizarre failure to insist on a key.

Tom

Jeff Moden SSC Guru Points: 1004191 More actions · Answer 8

I stand corrected. Thanks, Tom.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dwain Camps SSC Guru Points: 86948 More actions · Answer 9

Perhaps one of these is the shorthand the OP is looking for?

Select *

from TEST

where ISNULL(Col1,'')+ISNULL(Col2,'')+ISNULL(Col3,'')+ISNULL(Col4,'') = ''

Select *

from TEST

where COALESCE(Col1,Col2,Col3,Col4) IS NULL

Could it be that the rows have a primary key column (that is not NULL) not shown in the example?

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1004191 More actions · Answer 10

dwain.c (4/1/2012)
Perhaps one of these is the shorthand the OP is looking for?
Select *
from TEST
where ISNULL(Col1,'')+ISNULL(Col2,'')+ISNULL(Col3,'')+ISNULL(Col4,'') = ''
Select *
from TEST
where COALESCE(Col1,Col2,Col3,Col4) IS NULL
Could it be that the rows have a primary key column (that is not NULL) not shown in the example?

Absolutely. I was looking for a method where listing each column name wasn't necessary and made a bad assumption.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TomThomson SSC Guru Points: 104773 More actions · Answer 11

dwain.c (4/1/2012)
Perhaps one of these is the shorthand the OP is looking for?
Select *
from TEST
where ISNULL(Col1,'')+ISNULL(Col2,'')+ISNULL(Col3,'')+ISNULL(Col4,'') = ''

That one would require that every column of the table has a type to which varchar is implicitly convertible and which is implicitly convertible to varchar, for which '' is converted to that type without any conversion error, and that the type precedences of all those types are such that the + operator does what you expect it to do. I am pretty sure that that means that every column is CHAR or NVARCHAR or a numeric, no other types allowed, and the case where some of the columns are numeric it doesn't work because it doesn't cater for arithmetic overflow so just what combinations of nummeric types you can have is rather restricted. Even when that works, it blows up for combinations of zero length strings and 0 - any row where every column is either a zero length string or 0 in some numeric type will be shown as having every column NULL, which is a pretty nasty error.
Select *
from TEST
where COALESCE(Col1,Col2,Col3,Col4) IS NULL

That works fine: but the OP wanted a solution that didn't require him to type every column name. It's actually easy enough to write SQL to generate the required code, of course, and I reckon your coalesce solution is the best one to generate. So that works as long as cut and paste (from teh result of generating the statement to where it's to be executed) doesn't count as typing.

Tom