case expression in where clause and null's

Using SQL Server 2005
I have a simple table example with two columns: FirstName varchar(20) and
LastName varchar(20)
I am doing something like this in a stored procedure where @.firstname and
@.lastname are passed in and @.lastname could be null
select * from table where FirstName = @.firstname and LastName =
COALESCE(@.LastName, LastName)
I am also using "set ansi_nulls off".
The query should give names where LastName is null if @.Lastname = null but
that's not happening. Why?
John DalbergBecause COALESCE returns the first non-NULL value. If @.LastName is null,
then it won't return @.LastName, it will Return LastName (the column, not the
variable).
Essentially making your stmt:
select * from table where FirstName = @.firstname and LastName = LastName
or rather
select * from table where FirstName = @.firstname
"John Dalberg" <nospam@.nospam.sss> wrote in message
news:20060405191639.387$oV@.newsreader.com...
> Using SQL Server 2005
> I have a simple table example with two columns: FirstName varchar(20) and
> LastName varchar(20)
> I am doing something like this in a stored procedure where @.firstname and
> @.lastname are passed in and @.lastname could be null
> select * from table where FirstName = @.firstname and LastName =
> COALESCE(@.LastName, LastName)
> I am also using "set ansi_nulls off".
> The query should give names where LastName is null if @.Lastname = null but
> that's not happening. Why?
> John Dalberg|||>The query should give names where LastName is null if @.Lastname = null but
>that's not happening. Why?
It sounds like this might be confusion over how NULL works.
If table.LastName IS NULL, and @.LastName IS NULL, then
COALESCE(@.LastName, LastName) will resolve to NULL. In that situation
the test:
LastName = COALESCE(@.LastName, LastName)
resolves to:
NULL = NULL
Which comparison will never resolve to TRUE. NULL is never equal to
anything, including another NULL. Consider these comparisons:
NULL = 'banana'
NULL <> 'banana'
NULL = NULL
NULL <> NULL
None of those can ever be resolved as TRUE, because equality (or
inequality) can only result from comparing something to something.
NULL is nothing, and can not be compared at all.
Roy Harvey
Beacon Falls, CT
On 05 Apr 2006 23:06:02 GMT, nospam@.nospam.sss (John Dalberg) wrote:

>Using SQL Server 2005
>I have a simple table example with two columns: FirstName varchar(20) and
>LastName varchar(20)
>I am doing something like this in a stored procedure where @.firstname and
>@.lastname are passed in and @.lastname could be null
>select * from table where FirstName = @.firstname and LastName =
>COALESCE(@.LastName, LastName)
>I am also using "set ansi_nulls off".
>The query should give names where LastName is null if @.Lastname = null but
>that's not happening. Why?
>John Dalberg|||Roy Harvey <roy_harvey@.snet.net> wrote:
> It sounds like this might be confusion over how NULL works.
> If table.LastName IS NULL, and @.LastName IS NULL, then
> COALESCE(@.LastName, LastName) will resolve to NULL. In that situation
> the test:
> LastName = COALESCE(@.LastName, LastName)
> resolves to:
> NULL = NULL
> Which comparison will never resolve to TRUE. NULL is never equal to
> anything, including another NULL. Consider these comparisons:
> NULL = 'banana'
> NULL <> 'banana'
> NULL = NULL
> NULL <> NULL
> None of those can ever be resolved as TRUE, because equality (or
> inequality) can only result from comparing something to something.
> NULL is nothing, and can not be compared at all.
But when you have set ansi_nulls off and run:
select * from table where lastname = null, it returns rows where lastname=
null
Doesn't that statement translate to:
select * from table where null = null ?
and when you set ansi_nulls on
one needs to write it as: select * from table where lastname is null ?
John Dalberg|||take a look at this
declare @.v1 int,@.v2 int
select @.v1 = null,@.v2 = null
if @.v1 =@.v2
print 'equal'
else
print 'not equal'
go
declare @.v1 int,@.v2 int
select @.v1 = null,@.v2 = null
if @.v1 is null and @.v2 is null
print 'Both null'
else
print 'both not null'
go
set ansi_nulls off
go
declare @.v1 int,@.v2 int
select @.v1 = null,@.v2 = null
if @.v1 =@.v2
print 'equal'
else
print 'not equal'
go
set ansi_nulls on
go
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||"Paul Wehland" <PaulWe@.REMOVE-ME.Avanade.com> wrote:
> Because COALESCE returns the first non-NULL value. If @.LastName is null,
> then it won't return @.LastName, it will Return LastName (the column, not
> the variable).
> Essentially making your stmt:
> select * from table where FirstName = @.firstname and LastName =
> LastName or rather
> select * from table where FirstName = @.firstname
What does COALESCE return in this case when there's no none null values?
(not that it makes sense)
select * from table where FirstName = @.firstname and LastName =
COALESCE(@.LastName, @.LastName)
Would that translate to:
select * from table where FirstName = @.firstname and LastName = null ?
Doesn't 'set ansi_null off' make 'null =null' evaluate to true?
Anyways, I need the where clause to include the lastname if @.lastname has a
value and return null lastnames rows if @.lastname is null. I couldn't find
a way to do it in a CASE expression. I can do it in a dynamic sql.
John Dalberg
> "John Dalberg" <nospam@.nospam.sss> wrote in message
> news:20060405191639.387$oV@.newsreader.com...|||>>What does COALESCE return in this case when there's no none null values?
(not that it makes sense)
COALESCE will return the first non NULL value
Here is an example
declare @.v1 int,@.v2 int,@.v3 int,@.v4 int
select @.v1 = null,@.v2 = null,@.v3 =4,@.v4 =8
select coalesce(@.v1,@.v2,@.v3,@.v4)
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||"SQL" <denis.gobo@.gmail.com> wrote:
> take a look at this
> declare @.v1 int,@.v2 int
> select @.v1 = null,@.v2 = null
> if @.v1 =@.v2
> print 'equal'
> else
> print 'not equal'
> go
> declare @.v1 int,@.v2 int
> select @.v1 = null,@.v2 = null
> if @.v1 is null and @.v2 is null
> print 'Both null'
> else
> print 'both not null'
> go
> set ansi_nulls off
> go
> declare @.v1 int,@.v2 int
> select @.v1 = null,@.v2 = null
> if @.v1 =@.v2
> print 'equal'
> else
> print 'not equal'
> go
> set ansi_nulls on
> go
I understand the principles. That's why I included set ansi_nulls off in my
clarification.|||It is easier to communicate using DDL. I've added DDL at the end of this tex
t so we can use that
from here on.
Let me see if I can re-phrase your question:
You have some rows which has NULL on the lastname column. You want to find t
hem using ANSI_NULLS OFF
and by passing in NULL in the @.lastname parameter of your stored procedure.
So, in the query, you
have the following condition:
AND LastName = COALESCE(@.LastName, LastName)
So, if if you pass NULL for the @.lastname parameter, the condition will tran
slate to:
AND LastName = LastName
And you wonder why that will not return the rows where you have NULL in the
lastname column. Is that
correct?
If so, read in Books Online about SET ANSI_NULLS OFF. It only comments about
comparsions between a
column and NULL, not between two columns where each has NULL. I guess that t
his is how Sybase
defined it some 20 years ago, and MS has kapt this behavior. You could do a
BOL feedback and ask
them to clarify this in the 2005 BOL.
DDL:
USE tempdb
CREATE TABLE t(firstname varchar(30) not null, lastname varchar(30) null)
insert into t (firstname, lastname)
VALUES('John', 'Dalberg')
insert into t (firstname, lastname)
VALUES('Franz', NULL)
SET ANSI_NULLS OFF
GO
CREATE PROC p
@.firstname varchar(30), @.lastname varchar(30)
AS
SELECT firstname, lastname
from t
where FirstName = @.firstname
AND LastName = COALESCE(@.LastName, LastName)
GO
EXEC p 'John', 'Dalberg'
EXEC p 'John', NULL
EXEC p 'Franz', NULL
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John Dalberg" <nospam@.nospam.sss> wrote in message news:20060406121819.765$iB@.newsreader.c
om...
> Roy Harvey <roy_harvey@.snet.net> wrote:
> But when you have set ansi_nulls off and run:
> select * from table where lastname = null, it returns rows where lastname=
> null
> Doesn't that statement translate to:
> select * from table where null = null ?
> and when you set ansi_nulls on
> one needs to write it as: select * from table where lastname is null ?
> John Dalberg|||"Paul Wehland" <PaulWe@.REMOVE-ME.Avanade.com> wrote:
> Because COALESCE returns the first non-NULL value. If @.LastName is null,
> then it won't return @.LastName, it will Return LastName (the column, not
> the variable).
> Essentially making your stmt:
> select * from table where FirstName = ffirstname and LastName =
> LastName or rather
Right and that's the way it should work so when you have ansi_nulls off,
lastname = lastname will return rows where lastname is null and it will not
return these rows if set ansi_nulls on. I am missing what was wrong in my
statement.
so:
set ansi_nulls off;select * from table where FirstName = firstname and
LastName =lastname
&
select * from table
both return the same # of rows regardless whether lastname is null or not.
John Dalbergsql