CODEWITHSUNDEEP
sqlsql-server
Justin Samuel
Justin Samuel

Reputation: 1083

NULL check in Sql

Please suggest whats the difference between:

1) WHERE student.name = ISNULL(@name, student.name)

And

2) WHERE (student.name = @name OR @name IS NULL)

Actually I had a issue bug assigned against my name where some of the records where skipped when I used the first method. But got corrected when I replaced it with eg 2.

Upvotes: 3

Views: 471

Answers (6)

Nahuel Fouilleul
Nahuel Fouilleul

Reputation: 19335

WHERE student.name = ISNULL(@name, student.name)

And

WHERE (student.name = @name OR @name IS NULL)

are equivalent except if student.name is null.

WHERE (student.name = @name OR @name IS NULL)

may be simplified :

WHERE COALESCE(student.name,'nul') = COALESCE(@name, student.name,'nul')

And

WHERE student.name = ISNULL(@name, student.name)

is equivalent to

WHERE (student.name = @name OR @name IS NULL AND student.name IS NOT NULL)

Upvotes: 1

codingbiz
codingbiz

Reputation: 26396

The second statement says Select records when student name matches @name or when no name was specified If @name is specified, select for that name. If @name is empty, select all records. This doesn't care about what value is in the table when @name is NULL

For the first statement, if @name is NULL, it will use student.name which is the same as the current row's student.name

When both @name and student.name are NULL

WHERE student.name = @name OR @name IS NULL -- the other part of the OR is true so records will be returned

and

WHERE student.name = ISNULL(@name, student.name) becomes WHERE NULL = NULL

because NULL = NULL returns false, no records will be returned for that row

Upvotes: 1

Aleksandar Vucetic
Aleksandar Vucetic

Reputation: 14953

Here is the difference

name |student.name  |first_result  |second_result |expected*
A    |A             |true          |true          |true
A    |B             |false         |false         |false
null |A             |true          |true          |true
A    |null          |false         |false         |false
null |null          |FALSE         |TRUE          |TRUE

*expected - expected by some folks, but not correct in our universe.

As you see, difference comes when both values are NULL, because in that case, your first WHERE evaluates into: null = null (which is FALSE).

You can check that in SQL Server with:

select case when null = null then 1 else 0 end

which will give you 0.

Upvotes: 1

rs.
rs.

Reputation: 27467

Check examples below and in your 2nd option you can check @name is null before checking name = @name.

declare @name varchar(10) = null
declare @table table (name varchar(10))

insert into @table 
select 'A'
UNION
SELECT null


---returns only A because null = column is always false
select * from @table where name = ISNULL(@name, name)

--returns all rows
select * from @table where (@name is null or name = @name)


set @name = 'A'
select * from @table where name = ISNULL(@name, name)
select * from @table where (@name is null or name = @name)

Upvotes: 0

podiluska
podiluska

Reputation: 51504

If you have the ANSI_NULLS system setting set otherwise, null = anything is false.

So the first query will not return any entries where student.name is null, even if @name is null, because the comparison evaluates to where null=null for entries where student.name is null, and null=null returns false

Upvotes: 0

Mike Christensen
Mike Christensen

Reputation: 91696

The second parameter for ISNULL() is a replacement value, meaning the value that will be substituted if the first parameter is null. Thus, if both @name and student.name were null, you'd essentially be writing:

student.name = NULL

Which equates to false (as null in SQL is considered unknown, and never equals anything).

In the second, you're using the IS operator to test for a null value, which will return true if @name is null.

Upvotes: 8

Related Questions