ISNULL And COALESCE Behavior Difference

Vladimir Oselsky notes an edge case where ISNULL and COALESCE can behave differently:

Even though we would expect to see both records returned we only get 1 record. Huh? This is exactly what puzzled a coworker, ofcourse query was not as simple as this one but same issue caused him to hit a road block.

In the case of COALESCE and OR methods, results are identical.

The underlying issue here is that the variable data type differs from the column’s data type, and exposes a difference in how COALESCE and ISNULL work.

Related Posts

Window Functions In SQL

Eleni Markou explains what window functions are: What we want is a table with an extra column which will represent the average price of all products belonging to the same category as the one on the current line. One approach to solve this problem is to calculate the average price per category using an aggregate […]

Read More


Kenneth Fisher explains a couple of database name functions in SQL Server: I’d never seen ORIGINAL_DB_NAME until recently and I thought it would be interesting to highlight it out, and in particular the difference between it and DB_NAME. I use DB_NAME and DB_ID fairly frequently in support queries (for example what database context is a query running from or what database are […]

Read More


October 2016
« Sep Nov »