SQL Server COALESCE() Function

SQL Server COALESCE() Function

The SQL Server COALESCE() function returns the first non-null value in a list of expressions. This function is useful for handling null values and providing default values.

Syntax

SELECT COALESCE(expression1, expression2, ...);

The COALESCE() function takes multiple arguments:

  • expression1, expression2, ...: The expressions to be evaluated. The function returns the first non-null value from these expressions.

Example SQL Server COALESCE() Function Queries

Let's look at some examples of SQL Server COALESCE() function queries:

1. Basic COALESCE() Example

SELECT COALESCE(NULL, 'default value') AS result;

This query returns 'default value' as it is the first non-null expression. The result will be:

result
--------------
default value

2. COALESCE() with Multiple NULL Values

SELECT COALESCE(NULL, NULL, 'first non-null', 'second non-null') AS result;

This query returns 'first non-null' as it is the first non-null expression. The result will be:

result
--------------
first non-null

3. COALESCE() with Column Values

SELECT id, COALESCE(middle_name, first_name, last_name) AS preferred_name
FROM employees;

This query returns the first non-null value from the middle_name, first_name, and last_name columns for each record in the employees table. The result will show the original id and the corresponding preferred_name.

4. COALESCE() with Variables

DECLARE @val1 VARCHAR(50) = NULL, @val2 VARCHAR(50) = NULL, @val3 VARCHAR(50) = 'non-null value';
SELECT COALESCE(@val1, @val2, @val3) AS result;

This query uses variables to store values and returns the first non-null value. The result will be:

result
--------------
non-null value

Full Example

Let's go through a complete example that includes creating a table, inserting data, and using the COALESCE() function.

Step 1: Creating a Table

This step involves creating a new table named contacts to store some sample data with potential null values.

CREATE TABLE contacts (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    middle_name VARCHAR(50),
    last_name VARCHAR(50)
);

In this example, we create a table named contacts with columns for id, first_name, middle_name, and last_name.

Step 2: Inserting Data into the Table

This step involves inserting some sample data into the contacts table.

INSERT INTO contacts (id, first_name, middle_name, last_name) VALUES (1, 'John', NULL, 'Doe');
INSERT INTO contacts (id, first_name, middle_name, last_name) VALUES (2, 'Jane', 'A.', 'Smith');
INSERT INTO contacts (id, first_name, middle_name, last_name) VALUES (3, NULL, NULL, 'Brown');

Here, we insert data into the contacts table.

Step 3: Using the COALESCE() Function

This step involves using the COALESCE() function to return the preferred name for each contact, prioritizing middle_name, first_name, and then last_name.

SELECT id, COALESCE(middle_name, first_name, last_name) AS preferred_name
FROM contacts;

This query retrieves the id and the preferred name for each row in the contacts table. The result will be:

id  preferred_name
--- --------------
1   John
2   A.
3   Brown

Conclusion

The SQL Server COALESCE() function is a powerful tool for handling null values and providing default values. Understanding how to use the COALESCE() function and its syntax is essential for effective data processing and manipulation in SQL Server.