SQL Server - Where condition is null(coloumn name, 0) - ?
Asked By anbu n on 23-Nov-14 09:14 PM
select * from tbl_name
where ISNULL(ID,0) = xxx
Here in above statement ,
if xxx = 0 then what is ISNULL(ID,0)
if xxx = 1 then what is ISNULL(ID,0)
if xxx = 122 then what is ISNULL(ID,0)
how does ISNULL(ID,0) works in where condition
Robbe Morris replied to anbu n on 23-Nov-14 10:04 PM
Same as it does everywhere else. ISNULL will either return the value of ID if it is not null or 0 if it is. So, it is saying the WHERE clause is either equal to some value or 0.
Honestly, this is a pretty silly use of ISNULL and would probably result in the an INDEX SEEK not being performed by the query optimizer. I can't think of a real world query on primary or foreign key like this that you would need to perform an ISNULL function on a column of this nature in WHERE clause.