SQL WHERE clause not returning rows when field has NULL value
- by JohnB
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)
