SQL COALESCE

The SQL COALESCE function is used to return the first non-null value from a list of expressions. It is commonly used to handle NULL values in queries, ensuring that meaningful default values are returned instead of NULL.

In this tutorial, we will explore the SQL COALESCE function, its syntax, and practical examples to understand how it works in real-world scenarios.

Syntax of SQL COALESCE Function

The basic syntax of the COALESCE function is as follows:

</>
Copy
COALESCE(expression1, expression2, ..., expressionN)

Explanation:

  • The function takes multiple expressions as arguments.
  • It returns the first non-null value from the provided expressions.
  • If all expressions are NULL, it returns NULL.

Step-by-Step Examples Using SQL COALESCE

1 Handling NULL Values in a Column

Let’s create an employees table to demonstrate how the COALESCE function can replace NULL values with default values.

</>
Copy
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    department VARCHAR(50)
);

Insert some sample data where some employees have NULL department values:

</>
Copy
INSERT INTO employees (name, department)
VALUES 
('Arjun', 'IT'),
('Ram', NULL),
('Priya', 'Finance'),
('Sneha', NULL);

Now, let’s use the COALESCE function to replace NULL values with ‘Not Assigned’:

</>
Copy
SELECT name, COALESCE(department, 'Not Assigned') AS department 
FROM employees;

Explanation:

  • If the department column has a value, it is displayed as is.
  • If the column contains NULL, ‘Not Assigned’ is returned instead.

2 Using COALESCE to Handle Multiple Expressions

The COALESCE function can also be used to check multiple columns and return the first available value. Let’s create a table where employees have multiple contact details:

</>
Copy
CREATE TABLE employee_contacts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    phone VARCHAR(20),
    email VARCHAR(50)
);

Insert sample data:

</>
Copy
INSERT INTO employee_contacts (name, phone, email)
VALUES 
('Arjun', NULL, 'arjun@example.com'),
('Ram', '9876543210', NULL),
('Priya', NULL, NULL),
('Sneha', '9998887776', 'sneha@example.com');

Retrieve contact details, prioritizing phone over email, and email over ‘No Contact Available’:

</>
Copy
SELECT name, COALESCE(phone, email, 'No Contact Available') AS contact_info
FROM employee_contacts;

Explanation:

  • If the phone is available, it is returned.
  • If phone is NULL, the email is returned.
  • If both phone and email are NULL, ‘No Contact Available’ is returned.

Conclusion

The SQL COALESCE function is used for handling NULL values effectively. In this tutorial, we covered:

  • The syntax and working of the COALESCE function.
  • Replacing NULL values with meaningful default values.
  • Using COALESCE to check multiple columns and return the first available value.

By using COALESCE, you can ensure that NULL values do not disrupt data retrieval, improving query reliability and user experience.