Coalesce SQL

Galaxy Glossary

What does the COALESCE function do in SQL?

The COALESCE function returns the first non-NULL expression in a list. It's useful for handling missing or null data in queries.
Sign up for the latest in SQL knowledge from the Galaxy Team!
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.

Description

The COALESCE function is a powerful tool in SQL for handling NULL values. It allows you to specify a list of expressions and returns the first non-NULL value from that list. If all expressions are NULL, it returns the last expression in the list. This is particularly helpful when dealing with data that might be missing or incomplete. For instance, in a customer database, a customer's phone number might be missing. Using COALESCE, you can provide a default value for the phone number if it's NULL, preventing errors or unexpected results in your queries. COALESCE is also useful for simplifying queries that need to handle different possible outcomes. Instead of writing multiple `CASE` statements or `IF` conditions, you can use COALESCE to concisely return the appropriate value.

Why Coalesce SQL is important

COALESCE is crucial for robust data handling in SQL. It prevents errors caused by NULL values and allows for more predictable query results. It simplifies code and improves readability by providing a concise way to handle missing data.

Example Usage


-- Sample table
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Phone VARCHAR(20)
);

INSERT INTO Customers (CustomerID, FirstName, LastName, Phone)
VALUES
(1, 'John', 'Doe', '555-1212'),
(2, 'Jane', 'Smith', NULL),
(3, 'Peter', 'Jones', '555-3456');

-- Using COALESCE to handle NULL phone numbers
SELECT
    CustomerID,
    FirstName,
    LastName,
    COALESCE(Phone, 'Not Available') AS PhoneNumber
FROM
    Customers;

Common Mistakes

Want to learn about other SQL terms?