Returning rows with NULL using WHERE statements

Hi All

I have only just started using SQL Server as part of a new job, and have come across something while using Select statements. When you do a query using the Where clause eg. Where city = 'London' it returns results for just London, if you then do the same but using Where city <> 'London' again the results are obvious, but records where the city field is NULL do not appear in either? I would have assumed them to appear as part of the second query...

As I said, this is probably a daft question but if anyone can shed some light on it, it'd be much appreciated.

take a look here http://sqlservercode.blogspot.com/2006/01/null-trouble-in-sql-server-land.html

you need to do Where city <> 'London' or city is null

because null compared to anything is unknown

even null compared to null is unknown take a look at this

declare @.i int,@.i2 int
select @.i ,@.i2
if @.i = @.i2
select 'yes'
else
select 'no'

assuming ansi nulls is on (which is the default and you should leave it like that)

if it is set to off you will get no as a result

SET ANSI_NULLS OFF
go
declare @.i int,@.i2 int
select @.i ,@.i2
if @.i = @.i2
select 'yes'
else
select 'no'
go

SET ANSI_NULLS ON

GO

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||That's great - thanks very much!