Your query should check null condition as well.

Query -

select id , v from #t where  v not in ('PC' , 'PT') OR v is null

To start off, NULL does not mean "no value" it means "Unknown value" in SQL Server. There is a session setting called ANSI_NULLS that could make your queries behave as you would like them to, however, it's deprecated and will be forced to ON (which you don't seem to like) in a future version: http://msdn.microsoft.com/en-us/library/ms188048.aspx

I get what you're trying to do, and to make a counter point I would ask if you've seen any queries generated by reporting services or something like Cognos? If so, you'll see exactly what you're describing you don't want to do. Why? Well because with a schema that allows nulls, that's the way to do it. I'm not saying it's a super awesome and great idea but it works all of the time.

What your designer could do is check to see if that column could even be null and if so the appropriate logic could step it and create the correct query. You could also have options such as "This column may be null, do you want those values?". I don't know the end-game per-se and writing your own dynamic querying tool is quite the deat when the logical consistencies are all factored in (such as this).

I would continue to do explicit null checking on columns that could possibly be null, sure it doesn't look the best but it works all of the time.

ANSI_NULL set option will work for now but NOT a good idea especially if you don't control the environment, plus it will be forced set ON later and cause errors where you'll need to re-write your application logic anyway. This is the way SQL Server works with NULLs.

Here is a more long-winded explanation which takes it step by step. We have:

select * from tab1 where col1 not in (select col2 from tab2)

We can replace the subquery with the values in tab2. This gives us:

select * from tab1 where col1 not in (1, null, 6 ,4, 7)

Let's move the NOT:

select * from tab1 where not (col1 in (1, null, 6 ,4, 7))

IN is just a shortcut for OR, so what we really have is:

select * from tab1 where NOT (col1 = 1 OR col1 = null OR col1 = 6 OR col = 4 OR col1 = 7)

Let's nu study this for some of the values for tab1.col1. We can start with 1. This gives us

NOT (1 = 1 OR ...)

and we can stop there. The list of OR will return TRUE and with NOT in front we get FALSE.

What if we try 5?

NOT (5= 1 OR 5 = null OR 5 = 6 OR 5 = 4 OR 5 = 7)

so that's

NOT (FALSE OR 5 = NULL OR FALSE OR FALSE OR FALSE)

What about 5 = NULL? Well NULL is an unknown value. It could be different from 5, but by chance it could be 5. So the outcome is not TRUE, not FALSE, but UNKNOWN. So we have:

NOT (FALSE OR UNKNOWN OR FALSE OR FALSE OR FALSE)

The result of all the OR is UNKNOWN and with NOT in front, the value is still UNKNOWN by the three-valued logic of SQL. But for WHERE to include a row, the condition for the row must be TRUE, and thus the row with 5 is filtered out as well.

Using NOT EXISTS as Dan showed is a very good solution. Not the least since [NOT] EXISTS is more powerful than IN. However, there is a simpler fix to the original query:

select * from tab1 where col1 not in (select col2 from tab2 WHERE col2 IS NOT NULL)

Check out the full reference on Books Online - by default ANSI_NULLS is on meaning you'd need to use the approach you have done. Otherwise, you could switch that setting OFF at the start of the query to switch the behaviour round.

When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.
...
When SET ANSI_NULLS is ON, all comparisons against a null value evaluate to UNKNOWN. When SET ANSI_NULLS is OFF, comparisons of all data against a null value evaluate to TRUE if the data value is NULL.

Here's a simple example to demonstrate the behaviour with regard to comparisons against NULL:

-- This will print TRUE
SET ANSI_NULLS OFF;
IF NULL <> 'A'
    PRINT 'TRUE'
ELSE
    PRINT 'FALSE'

-- This will print FALSE
SET ANSI_NULLS ON;
IF NULL <> 'A'
    PRINT 'TRUE'
ELSE
    PRINT 'FALSE'

Just add a condition for the case of SerialNo is NULL. With your actual condition, this case is rejected from selection

SELECT t1.IPAddress,
t1.DNSRecord,
t2.SerialNo,
t2.IPAddress
FROM tableA t1
JOIN tableB t2 ON t1.IPAddress = t2.NetworkAddress
WHERE
IPAddress LIKE '%' +@IPAddress + '%'
AND ( SerialNo LIKE '%' +@SerialNo +'%' OR SerialNo is NULL)

An in statement will be parsed identically to field=val1 or field=val2 or field=val3. Putting a null in there will boil down to field=null which won't work.

(Comment by Marc B)

I would do this for clairity

SELECT *
FROM tbl_name
WHERE 
(id_field IN ('value1', 'value2', 'value3') OR id_field IS NULL)