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.