Coalesce

COALESCE is a SQL function that returns the first non-NULL expression among its arguments

Isnull

`ISNULL` is a SQL function that replaces NULL with the specified replacement value.

Null value quicker

Quality Example
More values

"I tend to use coalesce only when i need to get the first non-null value from a set of 3 or more values as isnull only supports two parameters"

from question "Sort query by field related to another field"

Quicker

"I understand the difference between these functions but my question is when checking for a single null value would isnull be any quicker than using coalesce"

from question "Which is quicker COALESCE OR ISNULL?"

More portable code

"Coalesce will go through the listed values and choose the first one that isn t null it s more portable code than isnull or ivnl etc"

from question "How to insert an alternative value from an old table into a new table IF NULL."

Faster lesser implementation

Quality Example
Better

"You will find that coalesce works better than isnull here"

from question "SQL Join on multiple OR conditions returns duplicates"

"An example of why coalesce is better than isnull"

from question "Select rows WHERE column = value or column is null (current solution is sloppy and slow)"

"Isnull performs better than the generic coalesce and better than having another and"

from question "SQL Query Date between, if date column not null"

"In most cases it has reviled indexes that needed to be add and in most cases the indexes improved the queries the most but after thet have been added the isnull and dynamic still perform better than the coalesce"

from question "SQL Where clause Coalese vs ISNULL VS Dynamic"

"But depending on the answers to those questions i m guessing that coalesce might do the trick for you better than isnull"

from question "SQL like Clause (null and blank values)"

"Is the performance of coalesce field constant better than isnull"

from question "Performance of ISNULL and COALESCE with a constant as second value"

Faster

"Isnull is faster than coalesce"

from question "SQL - Difference between COALESCE and ISNULL?"

"This is pretty much the ifloop answer but isnull is slightly faster than coalesce"

from question "Best way to do a CASE where clause SQL Server"

"Isnull is marginally faster than coalesce"

from question "Why IsNull is twice slow as coalesce (same query)?"

"Isnull will be faster i think because it has lesser function code implementation for itself making it faster than coalesce"

from question "Which is quicker COALESCE OR ISNULL?"

"Isnull can only have one input however it s been shown to be slightly faster than coalesce"

from question "SQL - Difference between COALESCE and ISNULL?"

"In some circumstances isnull is faster than case or coalesce"

from question "Coalesce vs Case"

Better option

"Edit based on the tests done by multiple people and by theory isnull seems to be a better option over coalesce"

from question "Substitute FROM ISNULL in where clause in ms sql server"

Comparatively faster

"You can use isnull also in place of coalesce as isnull is comparatively faster than coalesce"

from question "SQL increment integer column even if null"

Better then

"Isnull is better then coalesce because of how datatypes are handled"

from question "Question regarding index"

Others

Quality Example
Better

"On the subject of performance on sql server isnull often performs better than coalesce but the latter is ansi compliant if that is important to you"

from question "Boolean equivalence"

"Using coalesce is better option than isnull or case..when for this problem since the input values for the coalesce expression can be evaluated multiple times"

from question "Syntax error when assigning column value using CASE WHEN in Computed Column Formula"

"Sql server is probably smart enough to translate isnull into the equivalent sarg expression but if you are bent on using a function then coalesce is a better choice because it is part of the sql standard allows for multiple values instead of just two with isnull and avoids using quite possibly the most confusing function name microsoft ever devised in isnull"

from question "Sargable queries using ISNULL in TSQL"

More correct

"Coalesce is more correct of course you could just embed isnull over and over and over... but put that under a performance microscope and coalesce may just win"

from question "IsNULL and Coalesce proper usage"

More efficient

"To prefer isnull over coalesce when given the choice is that isnull tends to produce query plans that are more efficient than coalesce"

from question "Which is quicker COALESCE OR ISNULL?"

More cross-compatible

"Coalesce is more cross-compatible than isnull or nvl it works on mssql oracle mysql derby et al."

from question "SQL LEFT JOIN return 0 rather than NULL"

More portable

"- coalesce should be more portable than isnull"

from question "Incompatible SQL trigger"

More

"Coalesce is the more standard alternative of isnull"

from question "Using MS SQL ISNULL() with Alias"

"You can also use coalesce which is the more general form of isnull and is actually part of the sql standard"

from question "C# ADO.NET - return sum of one columns in table"

More levels

"In this case i would use coalesce which provides more levels than isnull rather than the case stement"

from question "COALESCE or CASE more efficient and/or standard"

Less

"I ve been told that coalesce is less costly than isnull but research doesn t indicate that"

from question "COALESCE Function in TSQL"

Back to Home
Data comes from Stack Exchange with CC-BY-SA-3.0