I am new to MSSQL and have a question regarding NULL/UNKOWN values in a where clause.
Let's say I have the following query:
SELECT [a],[b],[c] FROM
FROM ( SELECT 1 [a], 2 [b], 2 [c]) s
UNION ( SELECT NULL [a], 3 [b], 2 [c])
UNION ( SELECT NULL [a], 3 [b], 3 [c])
UNION ( SELECT 2 [a], NULL [b], NULL [c])) t
with the following results:
a b c
----------- ----------- -----------
NULL 3 2
NULL 3 3
1 2 2
2 NULL NULL
Now I want to return the rows where a!=1 and c!=2. But I do want to include those rows where either of them is NULL, as long as the other condition is met. So I want to add a where clause which causes the 2nd and 3rd rows to be returned.
The where clause
WHERE NOT ([a]=1 or [c]=2)
won't work since it will also leave the rows out where everything between NOT() is NULL/UNKOWN.
I could rewrite it to
WHERE NOT(isnull([a],0)=1 or isnull([c],0)=2)
which returns the correct result, but with more complex conditions inside NOT() that quickly becomes unreadable.
I also tried
WHERE ([a]=1 or [c]=2) IS NOT TRUE
which works in postgresql, but this is not valid in t-sql.
I hope anyone can help with this.
View Complete Post