What does SQL COALESCE do?

COALESCE evaluates each argument from left to right and returns the first argument that is not NULL. If all arguments are NULL, COALESCE returns NULL.

How many arguments can COALESCE accept?

COALESCE accepts two or more arguments. It is not limited to two expressions — you can pass as many fallback values as needed.

Is COALESCE equivalent to a CASE expression?

Yes. COALESCE(a, b, c) is equivalent to CASE WHEN a IS NOT NULL THEN a WHEN b IS NOT NULL THEN b ELSE c END.

What is the difference between COALESCE and ISNULL/NVL?

COALESCE is ANSI SQL standard and accepts multiple arguments. ISNULL (SQL Server) and NVL (Oracle) are database-specific and accept exactly two arguments. Prefer COALESCE for portability.

SQL COALESCE Function

SQL > SQL NULL > Coalesce Function

The COALESCE function in SQL returns the first non-NULL expression among its arguments. The syntax for COALESCE is as follows:

COALESCE scans its list of arguments from left to right and returns the first one that is not NULL — a versatile, ANSI-standard way to substitute fallback values for NULLs.

COALESCE ("expression 1", "expressions 2", ...)

It is the same as the following CASE statement:

SELECT CASE ("column_name")
   WHEN "expression 1 is not NULL" THEN "expression 1"
   WHEN "expression 2 is not NULL" THEN "expression 2"
   ...
   [ELSE "NULL"]
   END
FROM "table_name";

For examples, say we have the following table,

Table Contact_Info

Name	Business_Phone	Cell_Phone	Home_Phone
Jeff	531-2531	622-7813	565-9901
Laura	NULL	772-5588	312-4088
Peter	NULL	NULL	594-7477

and we want to find out the best way to contact each person according to the following rules:

1. If a person has a business phone, use the business phone number.

2. If a person does not have a business phone and has a cell phone, use the cell phone number.

3. If a person does not have a business phone, does not have a cell phone, and has a home phone, use the home phone number.

We can use the COALESCE function to achieve our goal:

SELECT Name, COALESCE (Business_Phone, Cell_Phone, Home_Phone) Contact_Phone 
FROM Contact_Info;

Result:

Name	Contact_Phone
Jeff	531-2531
Laura	772-5588
Peter	594-7477

Frequently Asked Questions

What does COALESCE return if all arguments are NULL?: If every argument evaluates to NULL, COALESCE returns NULL.
How many arguments can COALESCE take?: COALESCE accepts two or more arguments. Unlike ISNULL or NVL, which only accept two, COALESCE lets you chain as many fallback expressions as needed.
Is COALESCE available in all major databases?: Yes. COALESCE is defined in the ANSI SQL standard and is supported in Oracle, SQL Server, MySQL, PostgreSQL, SQLite, and most other relational databases.
How is COALESCE different from ISNULL or NVL?: ISNULL (SQL Server) and NVL (Oracle) accept exactly two arguments. COALESCE is the portable, standard-SQL equivalent that handles multiple fallback values in one call.

Next: SQL NULLIF

This page was last updated on March 19, 2026.

AdBlock Detected!

Our website is made possible by displaying ads to our visitors. Please supporting us by whitelisting our website.

SQL COALESCE Function

Frequently Asked Questions

Related Topics

AdBlock Detected!