Hey everyone,

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
(SELECT *
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.

Cheers,

Dolf.

**View Complete Post**

## Advanced Basics: Handling Null Values with Controls

Ken Spencer

MSDN Magazine March 2003