Question about Nulls in SELECT statement

Hi
Can someone please help me. I have 2 code examples that (i thought)
should produce the same results. The field in question (intNumber)
does allow Nulls. However, the results from Code 1 include the Null
record but the results from Code 2 do not.
- Table Data -
intNumber
4
5
NULL
- Code 1 -
SET ANSI_NULLS OFF
DECLARE @.intVar int
SELECT @.intVar = 4
SELECT * FROM tblTest WHERE (intNumber <> @.intVar)
- Results 1 -
5
NULL
- Code 2 -
SET ANSI_NULLS OFF
SELECT * FROM tblTest WHERE (intNumber <> 4)
- Results 2 -
5
Many thanks.
JuliaYes, the results are different. I strongly recommend that if possible
you avoid the ANSI_NULLS OFF setting. ANSI_NULLS OFF is a legacy
feature and most people recognize ANSI_NULLS ON as the standard to be
adopted for new code since SQL Server version 7.0.
If you must use ANSI_NULLS OFF then you'll have to cope with its
peculiarities (yes, even more peculiar than ANSI NULLs!). NULLs are
treated differently in constants, columns and variables.
David Portas
SQL Server MVP
--|||Hi David
Thanks for your reply and I take on board what you are saying about
ANSI_NULLS.
So now I have a different question really – I have a table that allows Nul
ls
in certain columns. How to I find these records in a select statement, i.e.
I want the Null records also when I say all records where intNumber <> 4. I
s
the ‘best practice’ solution to also include ‘or intNumber is null’?
Or
should I not be using Nulls at all? The reason is that my table is actually
representing data that can be inherited from another source – I used null
fields to recognize that this particular bit of data is not set in this tabl
e
but can be found else where.
Many thanks.
Julia.|||To return all NULL rows:
WHERE COALESCE(intNumber,0) <> 4
WHERE intNumber IS NULL OR intNumber <> 4
To return no NULL rows:
WHERE COALESCE(intNumber,4) <> 4
WHERE intNumber IS NOT NULL AND intNumber <> 4
(What is an intNumber, anyway? Kind of a funny name for a piece of data.)
"Julia Beresford" <JuliaBeresford@.discussions.microsoft.com> wrote in
message news:71C4EB9D-F709-48B3-99D1-465C9232E49A@.microsoft.com...
> Hi David
> Thanks for your reply and I take on board what you are saying about
> ANSI_NULLS.
> So now I have a different question really – I have a table that allows
> Nulls
> in certain columns. How to I find these records in a select statement,
> i.e.
> I want the Null records also when I say all records where intNumber <> 4.
> Is
> the ‘best practice’ solution to also include ‘or intNumber is
> null’? Or
> should I not be using Nulls at all? The reason is that my table is
> actually
> representing data that can be inherited from another source – I used
> null
> fields to recognize that this particular bit of data is not set in this
> table
> but can be found else where.
> Many thanks.
> Julia.
>|||That's the standard way, so your query would be:
select
*
from
MyTable
where
intNumber <> 4
or intNumber is null
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Julia Beresford" <JuliaBeresford@.discussions.microsoft.com> wrote in
message news:71C4EB9D-F709-48B3-99D1-465C9232E49A@.microsoft.com...
Hi David
Thanks for your reply and I take on board what you are saying about
ANSI_NULLS.
So now I have a different question really – I have a table that allows Nul
ls
in certain columns. How to I find these records in a select statement, i.e.
I want the Null records also when I say all records where intNumber <> 4.
Is
the ‘best practice’ solution to also include ‘or intNumber is null’?
Or
should I not be using Nulls at all? The reason is that my table is actually
representing data that can be inherited from another source – I used null
fields to recognize that this particular bit of data is not set in this
table
but can be found else where.
Many thanks.
Julia.