Reputation: 1083
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
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
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
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
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
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
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