{"id":1490,"date":"2019-03-03T21:06:58","date_gmt":"2019-03-03T14:06:58","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=1490"},"modified":"2020-07-11T03:50:04","modified_gmt":"2020-07-10T20:50:04","slug":"sql-server-coalesce","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-coalesce\/","title":{"rendered":"SQL Server COALESCE"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server COALESCE<\/code> expression to deal with NULL in queries.<\/p>\n\n\n\n

Introduction to SQL Server COALESCE expression #<\/a><\/h2>\n\n\n\nThe SQL Server COALESCE<\/code> expression accepts a number of arguments, evaluates them in sequence, and returns the first non-null argument.<\/p>\n\n\n\n The following illustrates the syntax of the COALESCE<\/code> expression:<\/p>\n\n\nCOALESCE(e1,[e2,...,en])\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\nIn this syntax, e1, e2, … en are scalar expressions that evaluate to scalar values. The COALESCE<\/code> expression returns the first non-null expression. If all expressions evaluate to NULL, then the COALESCE<\/code> expression return NULL;<\/p>\n\n\n\nBecause the COALESCE<\/code> is an expression, you can use it in any clause that accepts an expression such as SELECT<\/a><\/code>, WHERE<\/a><\/code>, GROUP BY<\/code><\/a>, and HAVING<\/a><\/code>.<\/p>\n\n\n\n SQL Server COALESCE expression examples #<\/a><\/h2>\n\n\n\nLet’s see practical examples of using the COALESCE<\/code> expression<\/p>\n\n\n\nA) Using SQL Server COALESCE expression with character string data example #<\/a><\/h3>\n\n\n\nThe following example uses the COALESCE<\/code> expression to return the string 'Hi'<\/code> because it is the first non-null argument:<\/p>\n\n\nSELECT<\/span> \n COALESCE<\/span>(NULL<\/span>, 'Hi'<\/span>, 'Hello'<\/span>, NULL<\/span>) result<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\nHere is the output:<\/p>\n\n\nresult\n------\nHi\n\n(1 row affected)<\/code><\/span><\/pre>\n\n\nB) Using SQL Server COALESCE expression with the numeric data example #<\/a><\/h3>\n\n\n\nThis example uses the COALESCE<\/code> expression to evaluate a list of arguments and to return the first number:<\/p>\n\n\nSELECT<\/span> \n COALESCE<\/span>(NULL<\/span>, NULL<\/span>, 100<\/span>, 200<\/span>) result<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\nThe output is as follows:<\/p>\n\n\nresult\n-----------\n100\n\n(1 row affected)<\/code><\/span><\/pre>\n\n\nC) Using SQL Server COALESCE expression to substitute NULL by new values #<\/a><\/h3>\n\n\n\nSee the following sales.customers<\/code> table from the sample database<\/a>.<\/p>\n\n\n\n<\/figure>\n\n\n\nThe following query returns first name, last name, phone, and email of all customers:<\/p>\n\n\nSELECT<\/span> \n first_name, \n last_name, \n phone, \n email\nFROM<\/span> \n sales.customers\nORDER<\/span> BY<\/span> \n first_name, \n last_name;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\nHere is the partial output:<\/p>\n\n\n\n<\/figure>\n\n\n\nThe phone<\/code> column will have NULL<\/code> if the customer does not have the phone number recorded in the sales.customers<\/code> table.<\/p>\n\n\n\n To make the output more business friendly, you can use the COALESCE<\/code> expression to substitute NULL<\/code> by the string N\/A<\/code> (not available) as shown in the following query:<\/p>\n\n\nSELECT<\/span> \n first_name, \n last_name, \n COALESCE<\/span>(phone,'N\/A'<\/span>) phone, \n email\nFROM<\/span> \n sales.customers\nORDER<\/span> BY<\/span> \n first_name, \n last_name;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\nThe following picture shows the partial output:<\/p>\n\n\n\n<\/figure>\n\n\n\nD) Using SQL Server COALESCE expression to use the available data #<\/a><\/h3>\n\n\n\nFirst, create a new table<\/a> named salaries<\/code> that stores the employee’s salaries:<\/p>\n\n\nCREATE<\/span> TABLE<\/span> salaries (\n staff_id INT<\/span> PRIMARY KEY<\/span>,\n hourly_rate decimal<\/span>,\n weekly_rate decimal<\/span>,\n monthly_rate decimal<\/span>,\n CHECK<\/span>(\n hourly_rate IS<\/span> NOT<\/span> NULL<\/span> OR<\/span> \n weekly_rate IS<\/span> NOT<\/span> NULL<\/span> OR<\/span> \n monthly_rate IS<\/span> NOT<\/span> NULL<\/span>)\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\nEach staff can have only one rate either hourly, weekly, or monthly.<\/p>\n\n\n\nSecond, insert<\/a> some rows into the salaries<\/code> table:<\/p>\n\n\nINSERT<\/span> INTO<\/span> \n salaries(\n staff_id, \n hourly_rate, \n weekly_rate, \n monthly_rate\n )\nVALUES<\/span>\n (1<\/span>,20<\/span>, NULL<\/span>,NULL<\/span>),\n (2<\/span>,30<\/span>, NULL<\/span>,NULL<\/span>),\n (3<\/span>,NULL<\/span>, 1000<\/span>,NULL<\/span>),\n (4<\/span>,NULL<\/span>, NULL<\/span>,6000<\/span>);\n (5,NULL, NULL,6500);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\nThird, query data<\/a> from the salaries<\/code> table:<\/p>\n\n\nSELECT<\/span>\n staff_id, \n hourly_rate, \n weekly_rate, \n monthly_rate\nFROM<\/span>\n salaries\nORDER<\/span> BY<\/span>\n staff_id;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\nHere is the output:<\/p>\n\n\n\n<\/figure>\n\n\n\nFourth, calculate monthly for each staff using the COALESCE<\/code> expression as shown in the following query:<\/p>\n\n\nSELECT<\/span>\n staff_id,\n COALESCE<\/span>(\n hourly_rate*22<\/span>*8<\/span>, \n weekly_rate*4<\/span>, \n monthly_rate\n ) monthly_salary\nFROM<\/span>\n salaries;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\nThe following picture shows the output:<\/p>\n\n\n\n<\/figure>\n\n\n\nIn this example, we used the COALESCE<\/code> expression to use only non-NULL value found in the hourly_rate<\/code>, weekly_rate<\/code>, and monthly_rate<\/code> columns.<\/p>\n\n\n\nCOALESCE vs. CASE expression #<\/a><\/h2>\n\n\n\nThe COALESCE<\/code> expression is a syntactic sugar of the CASE<\/code><\/a> expression.<\/p>\n\n\n\n The following expressions return the same result:<\/p>\n\n\n COALESCE(e1,e2,e3)\n\nCASE\n WHEN e1 IS NOT NULL THEN e1\n WHEN e2 IS NOT NULL THEN e2\n ELSE e3\nEND<\/span>\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\nNote that the query optimizer may use the CASE<\/code> expression to rewrite the COALESCE<\/code> expression.<\/p>\n\n\n\n In this tutorial, you have learned how to use the SQL Server COALESCE<\/code> expression to handle NULL values in queries.<\/p>\n \n\t