SQL WHERE clause not returning rows when field has NULL value

Question

Ok, so I'm aware of this issue:

When SET ANSI_NULLS is ON, all comparisons against a null value evaluate to UNKNOWN

• SQL And NULL Values in where clause
• SQL Server return Rows that are not equal <> to a value and NULL

However, I am trying to query a DataTable.

I could add to my query:

OR col_1 IS NULL OR col_2 IS NULL

for every column, but my table has 47 columns, and I'm building dynamic SQL (string concatenation), and it just seems like a pain to do that. Is there another solution?

I was to bring back all the rows that have NULL values in the WHERE comparison.

UPDATE

Example of query that gave me problems:

string query = col_1 not in ('Dorothy', 'Blanche') and col_2 not in ('Zborna', 'Devereaux')
grid.DataContext = dataTable.Select(query).CopyToDataTable();

(didn't retrieve rows if/when col_1 = null and/or col_2 = null)

Solution 1

So you mean something like (example with 2 columns)

WHERE (col1 = 'abc' or col1 is null)
  AND (col2 = 3 or col2 is null)

But you want to include the nulls always? This should work

WHERE isnull(col1,'abc') = 'abc'
  AND isnull(col2, 3) = 3

Solution 2

Are you doing this because you want to get the NULL values, or because you don't want the NULL values to interfere with your comparisons?

For example, this:

WHERE col_1 != 'John'

will not return any rows for which col_1 is NULL (you've indicated in your question that you know this).

If you're trying to get the col_1 IS NULL rows to return with a WHERE clause like the above, you have two options in MS SQL (one in mySql).

In both MS SQL and mySql, the coalesce() function will allow you to do a comparison of this sort. In MS SQL (I believe this is the same for mySql, too), its function is to take two or more arguments, and evaluate them in order, returning the first non-NULL one it finds. In MS SQL, there's a shorthand function called isNull(), which is like coalesce() but only takes two parameters.

In the above example, you would do the following:

WHERE coalesce(col_1,'') != 'John'

and that would return rows that have col_1 IS NULL as well as rows that have non-NULL values in col_1 which do not equate to 'John'.

If this is your real goal, then this technique should work for you.