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.
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.
@name of NULL, the first query would return any non-null student (such as 'bob' = 'bob') where-as the second query would return everything, as @name is null is true for all rows. Yes, it is rather an odd query.
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.
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
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)
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
WHERE student.name IS NULL OR student.name = @name ?
@Name is null if @Name is not null there's no differenceCheck 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)
