SQL Query to check if 40 columns in table is null .

Question

How do I select few columns in a table that only contain NULL values for all the rows? Suppose if Table has 100 columns, among this 100 columns 60 columns has null values. How can I write where condition to check if 60 columns are null.

Answer 1

where c1 is null and c2 is null ... and c60 is null

shortcut using string concatination (Oracle syntax):

where c1||c2||c3 ... c59||c60 is null

Answer 2

maybe with a COALESCE

SELECT * FROM table WHERE coalesce(col1, col2, col3, ..., colN) IS NULL

Answer 3

It would help to know which db you are using and perhaps which language or db framework if using one.

This should work though on any database.

Something like this would probably be a good stored procedure, since there are no input parameters for it.

select count(*) from table where col1 is null or col2 is null ...

Answer 4

Are you trying to find out if a specific set of 60 columns are null, or do you just want to find out if any 60 out of the 100 columns are null (not necessarily the same 60 for each row?)

If it is the latter, one way to do it in oracle would be to use the nvl2 function, like so:

select ... where (nvl2(col1,0,1)+nvl2(col2,0,1)+...+nvl2(col100,0,1) > 59)

A quick test of this idea:

select 'dummy' from dual where nvl2('somevalue',0,1) + nvl2(null,0,1) > 1

Returns 0 rows while:

select 'dummy' from dual where nvl2(null,0,1) + nvl2(null,0,1) > 1

Returns 1 row as expected since more than one of the columns are null.

Answer 5

First of all, if you have a table that has so many nulls and you use SQL Server 2008 - you might want to define the table using sparse columns (http://msdn.microsoft.com/en-us/library/cc280604.aspx).

Secondly I am not sure if coalesce solves the question asks - it seems like Ammu might actually want to find the list of columns that are null for all rows, but I might have misunderstood. Nevertheless - it is an interesting question, so I wrote a procedure to list null columns for any given table:

IF (OBJECT_ID(N'PrintNullColumns') IS NOT NULL)
    DROP PROC dbo.PrintNullColumns;
go
CREATE PROC dbo.PrintNullColumns(@tablename sysname)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @query nvarchar(max);
    DECLARE @column sysname;
    DECLARE columns_cursor CURSOR FOR
     SELECT c.name
     FROM sys.tables t JOIN sys.columns c ON t.object_id = c.object_id
     WHERE t.name = @tablename AND c.is_nullable = 1;
    OPEN columns_cursor;
    FETCH NEXT FROM columns_cursor INTO @column;
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
     SET @query = N'
     DECLARE @c int
     SELECT @c = COUNT(*) FROM ' + @tablename + ' WHERE ' + @column + N' IS NOT NULL
     IF (@c = 0)
      PRINT (''' + @column + N''');'
     EXEC (@query);

     FETCH NEXT FROM columns_cursor INTO @column;
    END
    CLOSE columns_cursor;
    DEALLOCATE columns_cursor;
    SET NOCOUNT OFF;
    RETURN;
END;
go