Question

  • I have a production database with a significant number of columns, and I am curious if there is a way to pull results from only rows that have non-NULL values, without specifying each specific column in the where clause?

  • You getting back Not Null is really not important because only aggregate functions ignore nulls because the aggregates are created for scalar value but a SELECT returns table. But you just add COUNT(*) to all aggregate operations so all rows can be counted before the aggregates are applied.

    Now there is OUTER JOIN null which Not Null or Null is not relevant because it defaults to a mathematical null because the null comes from one of the tables smaller than the other and you must use ISNULL to get the correct value. You should know the WHERE clause is not aware of Nulls and in the current JOIN operations the WHERE clause is just a filter as the AND operator. You will find all these informations in good ANSI SQL books.

    Kind regards,
    Gift Peddie

  • Question: Do you want to ignore a row unless ALL columns have values?

    If so, you are probably going to have to list all the columns. The easy way to do this is using SQL Server Management Studio to script a SELECT of the table in question. That will give you the full list of column names. Then a little search and replace magic would let you create the following

    WHERE columnA+columnB+columnC.... columnZZZ is not NULL

    The presence of any null value in that expression will cause the whole expression to be null, and therefore ignored. However, it is most definitely going to do a table scan in the execution plan.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Bob Hovious (2/19/2009)


    Question: Do you want to ignore a row unless ALL columns have values?

    If so, you are probably going to have to list all the columns. The easy way to do this is using SQL Server Management Studio to script a SELECT of the table in question. That will give you the full list of column names. Then a little search and replace magic would let you create the following

    WHERE columnA+columnB+columnC.... columnZZZ is not NULL

    The presence of any null value in that expression will cause the whole expression to be null, and therefore ignored. However, it is most definitely going to do a table scan in the execution plan.

    You could create a script that generates this kind of code for each table using the INFORMATION_SCHEMA views, but it would probably take as long as doing it manually. It would be an interesting exercise.

  • I'm sure that is how SQL Mgmt Studio scripts SELECTs, UPDATEs, and INSERTs.

    This isn't the first question that has asked if there isn't a way to avoid listing a lot of column names. But using SQL Mgmt Studio to script a SELECT, followed by a little cut-and-paste search-and-replace is pretty quick and straightforward.

    I could knock out a script/proc/function that would return a string based on the schema, but what other functionality could that script provide besides just putting '+' between the column names, instead of a comma?

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Bob,

    I don't disagree. I was thinking that you would use the INFORMATION_SCHEMA.COLUMNS table to only add columns to the WHERE clause that allow nulls. The only way something like that would be productive would be if you had to create queries like this for every table in your database that has nullable columns

    If I really thought it would be useful I probably would have written it already.

Viewing 6 posts - 1 through 6 (of 6 total)

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