{"id":875,"date":"2016-07-15T02:04:34","date_gmt":"2016-07-15T02:04:34","guid":{"rendered":"https:\/\/sqltutorial.org\/?page_id=875"},"modified":"2025-02-08T01:30:33","modified_gmt":"2025-02-08T08:30:33","slug":"sql-coalesce","status":"publish","type":"page","link":"https:\/\/www.sqltutorial.org\/sql-coalesce\/","title":{"rendered":"SQL COALESCE Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you’ll learn how to use the SQL COALESCE()<\/code> function to handle NULL<\/code> effectively.<\/p>\n\n\n\n

Introduction to the SQL COALESCE function #<\/a><\/h2>\n\n\n\n

In SQL, the COALESCE()<\/code> function takes one or more arguments and returns the first non-NULL argument.<\/p>\n\n\n\n

Here’s the syntax of the COALESCE<\/code> function:<\/p>\n\n\n

COALESCE(argument1, argument2,...);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The COALESCE<\/code> function evaluates its arguments from left to right and returns the first non-NULL argument.<\/p>\n\n\n\n
The COALESCE<\/code> function will return NULL<\/code> in case all input arguments are NULL<\/code>.<\/p>\n\n\n\n
Basic SQL COALESCE function examples #<\/a><\/h2>\n\n\n\n
The following example shows how to use the COALESCE<\/code> function with numbers 1, 2, and 3:<\/p>\n\n\n
SELECT<\/span>\n  COALESCE<\/span>(1<\/span>, 2<\/span>, 3<\/span>) AS<\/span> result<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 result\n--------<\/span>\n      1<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The result is 1 because it is the first non-NULL argument.<\/p>\n\n\n\n
The following statement returns the string SQL<\/code> because it is the first non-NULL argument.<\/p>\n\n\n
SELECT<\/span>\n  COALESCE<\/span>(NULL<\/span>, 'SQL'<\/span>, 'Tutorial'<\/span>) AS<\/span> result<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 result\n--------<\/span>\n SQL<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Short-circuit evaluation #<\/a><\/h2>\n\n\n\n
If you divide by zero, you’ll encounter an error:<\/p>\n\n\n
SELECT<\/span> 1<\/span>\/0<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
ERROR:  division by zero<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
But the following statement returns one instead of an error:<\/p>\n\n\n
SELECT<\/span>\n  COALESCE<\/span>(1<\/span>, 1<\/span> \/ 0<\/span>) result<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 result\n--------<\/span>\n      1<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The reason is that the COALESCE<\/code> function uses short-circuit evaluation. It means that the COALESCE<\/code> function does not evaluate the remaining arguments after it encounters the first non-NULL arguments.<\/p>\n\n\n\n
Using SQL COALESCE function to substitute NULL #<\/a><\/h2>\n\n\n\n
First, create a new table<\/a> called bonuses<\/code> to store employee bonuses:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> bonuses (\n  employee_id INT<\/span> PRIMARY KEY<\/span>,\n  amount DECIMAL<\/span>(10<\/span>, 2<\/span>) NULL<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Some employees may have bonuses so that the amount column can be NULL.<\/p>\n\n\n\n
Second, insert some rows<\/a> into the bonuses<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  bonuses (employee_id, amount)\nVALUES<\/span>\n  (101<\/span>, 1000.00<\/span>),\n  (102<\/span>, NULL<\/span>),\n  (103<\/span>, 1500.00<\/span>),\n  (104<\/span>, NULL<\/span>),\n  (105<\/span>, 2000.00<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, use the COALESCE<\/code> function to replace NULL<\/code> with zero when calculating the total compensation that includes both salary and bonus:<\/p>\n\n\n
SELECT<\/span>\n  e.first_name,\n  e.last_name,\n  e.salary,\n  e.salary + COALESCE<\/span>(b.amount, 0<\/span>) AS<\/span> total_compensation\nFROM<\/span>\n  employees e\n  LEFT<\/span> JOIN<\/span> bonuses b ON<\/span> e.employee_id = b.employee_id;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 first_name  |  last_name  |  salary  | total_compensation\n-------------+-------------+----------+--------------------<\/span>\n Neena       | Kochhar     | 17000.00 |           18000.00\n Lex         | De Haan     | 17000.00 |           17000.00\n Alexander   | Hunold      |  9000.00 |           10500.00\n Bruce       | Ernst       |  6000.00 |            6000.00\n...<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SQL COALESCE and CASE expression #<\/a><\/h2>\n\n\n\n
The COALESCE<\/code> function is functionally equivalent to the following CASE<\/a><\/code> expression:<\/p>\n\n\n
CASE\n  WHEN (argument1 IS NOT NULL) THEN argument1\n  WHEN (argument2 IS NOT NULL) THEN argument2\n  ELSE argument3\nEND<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span>\n  e.first_name,\n  e.last_name,\n  e.salary,\n  e.salary + CASE<\/span>\n    WHEN<\/span> b.amount IS<\/span> NULL<\/span> THEN<\/span> 0<\/span>\n    ELSE<\/span> b.amount\n  END<\/span> AS<\/span> total_compensation\nFROM<\/span>\n  employees e\n  LEFT<\/span> JOIN<\/span> bonuses b ON<\/span> e.employee_id = b.employee_id;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 first_name  |  last_name  |  salary  | total_compensation\n-------------+-------------+----------+--------------------<\/span>\n Neena       | Kochhar     | 17000.00 |           18000.00\n Lex         | De Haan     | 17000.00 |           17000.00\n Alexander   | Hunold      |  9000.00 |           10500.00\n Bruce       | Ernst       |  6000.00 |            6000.00\n...<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
The following query<\/a> uses the CASE<\/code> expression instead of the COALESCE<\/code> function:<\/p>\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n