I use NorthWind database. Why result is same(none)?

SELECT * FROM Customers WHERE Country NOT IN (null)

SELECT * FROM Customers WHERE Country IN (null)

http://msdn.microsoft.com/en-us/library/ms177682.aspx

I quote:

"Caution:
Any null values returned by subquery or expression that are compared to test_expression using IN or NOT IN return UNKNOWN. Using null values in together with IN or NOT IN can produce unexpected results."

If you read the docs on the = and <> operators, you can see why.

Yes results are strange

SELECT * FROM Customers WHERE Country IN ('Germany',null)

SELECT * FROM Customers WHERE Country NOT IN ('Germany',null)

Yes, you can't do that.

You need to do something like this:

SELECT * FROM Customers WHERE ISNULL(Country,'Germany') = 'Germany'
SELECT * FROM Customers WHERE ISNULL(Country,'Germany') <> 'Germany'

Wrapping a column name in the WHERE clause in a function in the presence of indexes will prevent an index seek causing the code to slow down quite a bit. Better to use an "OR".

--Jeff Moden

Good point.

Though I'm surprised that SQL Server doesn't handle ISNULL( ) as a special case. In fact, I give the SQL Server compiler people big minus points for not doing so, since it's such an obvious and easy optimization to make. [Assuming that you are right and they don't, that is.]

************

Joxa: What Jeff is saying is that you should code that as

SELECT * FROM Customers WHERE Country IS NULL OR Country = 'Germany'
SELECT * FROM Customers WHERE Country IS NOT NULL AND Country <> 'Germany'

Assuming that the Country field is indexed. (Won't matter much, if it's not.)

Actually, it does matter... if the proper indexing is missing, the ISNULL method actually works a bit faster than the OR.

I agree... stupid optimizer. :D

Personally, I'd use the OR method either way because there's always a chance that someone (or me) could add the proper indexing. If you use ISNULL, indexing will only help a tiny bit.

--Jeff Moden

No need for IS NOT NULL here

SELECT * FROM Customers WHERE <s>Country IS NOT NULL AND</s> Country <> 'Germany'

Thank you guys.:D