PrismitaLEARN · PRACTICE · GROW

What is the difference between ISNULL and COALESCE?

SQL Server T-SQL Basic Fresher1–2 Yrs SQL Query 1 min read

Quick Answer

ISNULL(a, b) is a T-SQL function taking exactly two arguments; the result type follows the first argument. COALESCE(a, b, c, …) is ANSI-standard, takes any number of arguments, returns the first non-null, and its result type follows data-type precedence rules. Prefer COALESCE for portability and multiple fallbacks.

ISNULL is a T-SQL-specific function with exactly two arguments, and its return type is whatever the first argument's type is — which can truncate a string unexpectedly. COALESCE is the ANSI-standard version, takes any number of arguments and returns the first non-null one, and its type follows precedence rules. I default to COALESCE because it's portable and handles multiple fallbacks, but it compiles to a CASE, so if one of the arguments is a subquery it can get evaluated twice.

  • Arguments: ISNULL — 2 only. COALESCE — 2 or more.
  • Type: ISNULL(CAST(NULL AS varchar(3)), 'ABCD') truncates to 'ABC' (type of arg 1). COALESCE uses precedence, so it may return 'ABCD'.
  • Nullability: ISNULL result is often treated as NOT NULL; COALESCE can still be nullable if all inputs are.
  • Evaluation: COALESCE is expanded to a CASE expression, so a subquery argument can be evaluated more than once — watch for that in performance-sensitive code.
  • ISNULL is slightly faster and simpler for the plain two-argument case.
All SQL Server questions More on T-SQL