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.