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

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???

You pretty much have it. You could use built-in functions like ISNULL or NULLIF to reduce the amount of code. The functions may change the execution plan slightly, but essentially the logic will be the same.

Code:

SELECT  *
FROM    TEST
WHERE  ISNULL(Col1, '') = ''
        AND ISNULL(Col2, '') = ''
        AND ISNULL(Col3, '') = ''
        AND ISNULL(Col4, '') = ''
 
SELECT  *
FROM    TEST
WHERE  NULLIF(Col1, '') IS NULL
        AND NULLIF(Col2, '') IS NULL
        AND NULLIF(Col3, '') IS NULL
        AND NULLIF(Col4, '') IS NULL

This would also work for you:

Code:

SELECT  *
FROM    TEST
WHERE  '' IN (Col1, Col2, Col3, Col4)
        OR Col1 IS NULL
        OR Col2 IS NULL
        OR Col3 IS NULL
        OR Col4 IS NULL