{"id":608,"date":"2019-08-21T20:36:36","date_gmt":"2019-08-21T15:06:36","guid":{"rendered":"https:\/\/techieshouts.com\/?p=608"},"modified":"2022-07-28T23:03:23","modified_gmt":"2022-07-28T17:33:23","slug":"difference-between-isnull-and-coalesce-in-sql","status":"publish","type":"post","link":"https:\/\/techieshouts.com\/home\/difference-between-isnull-and-coalesce-in-sql\/","title":{"rendered":"Difference between ISNULL and COALESCE in SQL"},"content":{"rendered":"\n<p>In this article, we will see the difference between ISNULL and COALESCE in SQL Server. Both ISNULL and COALESCE evaluates the expression for NULL and returns a non-null value.<\/p>\n\n\n\n<h2>ISNULL<\/h2>\n\n\n\n<p>The ISNULL function\nreplaces NULL with a specified value.<\/p>\n\n\n\n<p>ISNULL ( check_expression, replacement_value )<\/p>\n\n\n\n<p>It contains two parameters. The first parameter is to evaluate the expression for NULL. If the first parameter is NULL, the function replaces the second parameter.<\/p>\n\n\n\n<h2>COALESCE<\/h2>\n\n\n\n<p>COALESCE evaluates\nthe expression in order and returns the first non-null value.<\/p>\n\n\n\n<p>COALESCE\n( expression1, expression2 [, \u2026 expressionN] )<\/p>\n\n\n\n<p>It can contain N no of parameters. It returns the first non-null parameter value.<\/p>\n\n\n\n<h2>Difference between ISNULL and COALESCE<\/h2>\n\n\n\n<ul><li>ISNULL function returns the data type of the first parameter whereas, COALESCE returns the data type of expression with highest <a rel=\"noreferrer noopener\" aria-label=\"data type precedence (opens in a new tab)\" href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/data-types\/data-type-precedence-transact-sql?view=sql-server-2017\" target=\"_blank\">data type precedence<\/a>.<\/li><li> When the first parameter is NULL, ISNULL function implicitly converts the second parameter value to the data type of the first parameter.  <\/li><\/ul>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><img loading=\"lazy\" width=\"813\" height=\"365\" src=\"https:\/\/techieshouts.com\/wp-content\/uploads\/2019\/08\/Example2.png\" alt=\"ISNULL and COALESCE returns non-null value\" class=\"wp-image-609\" srcset=\"https:\/\/techieshouts.com\/wp-content\/uploads\/2019\/08\/Example2.png 813w, https:\/\/techieshouts.com\/wp-content\/uploads\/2019\/08\/Example2-300x135.png 300w, https:\/\/techieshouts.com\/wp-content\/uploads\/2019\/08\/Example2-768x345.png 768w, https:\/\/techieshouts.com\/wp-content\/uploads\/2019\/08\/Example2-660x296.png 660w\" sizes=\"(max-width: 813px) 100vw, 813px\" \/><\/figure><\/div>\n\n\n\n<ul><li>ISNULL function truncates the second parameter value when it is longer than the first parameter. Whereas, COALESCE doesn&#8217;t truncate any values. <\/li><\/ul>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><img loading=\"lazy\" width=\"667\" height=\"365\" src=\"https:\/\/techieshouts.com\/wp-content\/uploads\/2019\/08\/Example3.png\" alt=\"ISNULL returns the data type of the first parameter\" class=\"wp-image-610\" srcset=\"https:\/\/techieshouts.com\/wp-content\/uploads\/2019\/08\/Example3.png 667w, https:\/\/techieshouts.com\/wp-content\/uploads\/2019\/08\/Example3-300x164.png 300w, https:\/\/techieshouts.com\/wp-content\/uploads\/2019\/08\/Example3-660x361.png 660w\" sizes=\"(max-width: 667px) 100vw, 667px\" \/><\/figure><\/div>\n\n\n\n<ul><li>If the data type of both the parameters in ISNULL function is not determined, then the data type returned is an integer. In COALESCE expression, at least one of the NULL values must be a typed NULL. If the data type of all the parameters is not determined, then it throws an error.<\/li><\/ul>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"aligncenter\"><img loading=\"lazy\" width=\"874\" height=\"466\" src=\"https:\/\/techieshouts.com\/wp-content\/uploads\/2019\/08\/Example1-1.png\" alt=\"\" class=\"wp-image-613\" srcset=\"https:\/\/techieshouts.com\/wp-content\/uploads\/2019\/08\/Example1-1.png 874w, https:\/\/techieshouts.com\/wp-content\/uploads\/2019\/08\/Example1-1-300x160.png 300w, https:\/\/techieshouts.com\/wp-content\/uploads\/2019\/08\/Example1-1-768x409.png 768w, https:\/\/techieshouts.com\/wp-content\/uploads\/2019\/08\/Example1-1-660x352.png 660w\" sizes=\"(max-width: 874px) 100vw, 874px\" \/><\/figure><\/div>\n\n\n\n<ul><li>The query optimizer rewrites the COALESCE expression as a CASE expression like below. Whereas, the ISNULL function remains the same.<\/li><\/ul>\n\n\n\n<pre data-mode=\"sqlserver\" data-theme=\"dreamweaver\" data-fontsize=\"14\" data-lines=\"Infinity\" class=\"wp-block-simple-code-block-ace\">CASE  \n    WHEN (expression1 IS NOT NULL) THEN expression1  \n    WHEN (expression2 IS NOT NULL) THEN expression2 \n    ELSE expressionN  \nEND<\/pre>\n\n\n\n<ul><li>ISNULL function is evaluated only once whereas the COALESCE expression is evaluated multiple times.<\/li><\/ul>\n\n\n\n<p>Also, see <a href=\"https:\/\/techieshouts.com\/pagination-in-sql-server\/\">Pagination in SQL Server<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>In this article, we will see the difference between ISNULL and COALESCE in SQL Server. Both ISNULL and COALESCE evaluates the expression for NULL and returns a non-null value. ISNULL The ISNULL function replaces NULL with a specified value. ISNULL ( check_expression, replacement_value ) It contains two parameters. The first parameter is to evaluate the\u2026 <span class=\"read-more\"><a href=\"https:\/\/techieshouts.com\/home\/difference-between-isnull-and-coalesce-in-sql\/\">Read More &raquo;<\/a><\/span><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[15,10],"tags":[54,55,39,40],"_links":{"self":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts\/608"}],"collection":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/comments?post=608"}],"version-history":[{"count":6,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts\/608\/revisions"}],"predecessor-version":[{"id":1199,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/posts\/608\/revisions\/1199"}],"wp:attachment":[{"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/media?parent=608"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/categories?post=608"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techieshouts.com\/home\/wp-json\/wp\/v2\/tags?post=608"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}