SQL Server Performance

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

Discussion in 'ALL SQL SERVER QUESTIONS' started by bsethi24, Mar 27, 2012.

  1. bsethi24 New Member

    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???
  2. Orlando Colamatteo New Member

    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
  3. Shehap MVP, MCTS, MCITP SQL Server

    Also you could use coalesce commands as below

    SELECT

    *

    FROM

    TEST

    WHERE

    coalesce(Col1,'')=''

    ANDcoalesce(Col2,'')=''

    ANDcoalesce(Col3,'')=''

    ANDcoalesce(Col4,'')=''
  4. Orlando Colamatteo New Member

    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 
  5. Madhivanan Moderator

    or

    select * from test
    where coalesce(col1,col2,col3,col4) is null or col1+col2+col3+col4=''

Share This Page