Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old September 25th, 2008, 10:10 AM
Authorized User
Points: 202, Level: 4
Points: 202, Level: 4 Points: 202, Level: 4 Points: 202, Level: 4
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2005
Location: Serbia, Belgrade
Posts: 44
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to joxa83 Send a message via Yahoo to joxa83
Default IN and NULLs

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

SELECT * FROM Customers WHERE Country NOT IN (null)

SELECT * FROM Customers WHERE Country IN (null)

  #2 (permalink)  
Old September 25th, 2008, 01:22 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.
  #3 (permalink)  
Old September 26th, 2008, 03:38 AM
Authorized User
Points: 202, Level: 4
Points: 202, Level: 4 Points: 202, Level: 4 Points: 202, Level: 4
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2005
Location: Serbia, Belgrade
Posts: 44
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to joxa83 Send a message via Yahoo to joxa83
Default

Yes results are strange

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

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


  #4 (permalink)  
Old September 26th, 2008, 04:07 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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'
  #5 (permalink)  
Old September 26th, 2008, 10:40 PM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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
  #6 (permalink)  
Old September 27th, 2008, 01:58 AM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.)
  #7 (permalink)  
Old September 27th, 2008, 08:12 AM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

Quote:
quote:Originally posted by Old Pedant


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
  #8 (permalink)  
Old September 29th, 2008, 01:00 AM
Friend of Wrox
 
Join Date: May 2006
Location: Helsingborg, , Sweden.
Posts: 246
Thanks: 0
Thanked 0 Times in 0 Posts
Default

No need for IS NOT NULL here

SELECT * FROM Customers WHERE <s>Country IS NOT NULL AND</s> Country <> 'Germany'
  #9 (permalink)  
Old September 29th, 2008, 03:03 AM
Authorized User
Points: 202, Level: 4
Points: 202, Level: 4 Points: 202, Level: 4 Points: 202, Level: 4
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2005
Location: Serbia, Belgrade
Posts: 44
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to joxa83 Send a message via Yahoo to joxa83
Default

Thank you guys.:D



Similar Threads
Thread Thread Starter Forum Replies Last Post
Working around DB Nulls chroniclemaster1 ASP.NET 2.0 Basics 2 February 27th, 2008 03:11 AM
WHERE Clause, LIKE, and Nulls jurio SQL Language 5 May 28th, 2007 02:53 AM
Find Nulls jemacc SQL Server 2000 1 September 15th, 2004 01:28 PM
looking for nulls Warbird C# 4 July 1st, 2004 07:50 AM
Handling Nulls shahchi1 ADO.NET 4 June 24th, 2004 11:29 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.