Using COALESCE in SQL: A Beginner’s Guide
What is the COALESCE Function in SQL?
Coalesce is essentially a NULL killer. If you have a dataset in SQL that has a lot of NULL values, we can use COALESCE function to replace the NULL values. However, COALESCE is a versatile SQL function that can be used in a wide range of scenarios where NULL values may be present. By allowing you to define default values and handle NULL values more effectively, COALESCE can help you create more robust, error-resistant SQL code.
Pros and Cons of COALESCE
|Allows for cleaner, more concise SQL code||May decrease performance in certain scenarios|
|Can help prevent errors caused by NULL values||Can make SQL code less readable for some users|
|Provides flexibility with default values||Can lead to unexpected results if used improperly|
|Can be used in a variety of scenarios||Can result in data loss if default values not chosen carefully|
Basic Functionality and Syntax of COALESCE
It returns the first non-NULL value from its arguments. If all arguments are NULL, it returns the default value.
COALESCE(value1, value2, value3, ..., default_value)
We combine this function with a SELECT query in the following manner to retrieve the first non-null value from the column and provide an alternative if conditions are NULL
SELECT COALESCE(column1,'No value found') AS value FROM my_table;
You are not limited to a single column within the function multiple columns can be selected. n this query,
column3 are evaluated by the COALESCE function, and the first non-NULL value is returned. If all three columns are NULL, the function returns the string ‘No value found’.
Use-Cases for COALESCE in SQL
Replacing NULL values with a default value:
SELECT name, COALESCE(phone_number, 'N/A') AS phone_number FROM customers;
This query will return an alternative provided by the query. In this particular example, a list of customer names and phone numbers. If a customer does not have a phone number in the database, the COALESCE function will replace the NULL value with the string ‘N/A’.
Combining data from multiple columns or tables
SELECT COALESCE(salary1, salary2, salary3) AS salary FROM employee_salaries;
In this particular query will return a list of employee salaries, selecting the first non-NULL salary value encountered from three different salary columns in the
Handling data in conditional expressions with NULLs
SELECT id, name, CASE WHEN COALESCE(salary, 0) > 50000 THEN 'High Salary' ELSE 'Low Salary' END AS salary_type FROM employees;
If the salary is NULL, the COALESCE function will replace it with a default value of 0.
Reducing the number of NULL in SQL Calculations
SELECT COALESCE(price, 0) * COALESCE(quantity, 1) AS total_cost FROM orders
The COALESCE function is used to replace NULL values with default values of 0 for the price column and 1 for the quantity column. Alternative to calculation when adding can be seen below
SELECT COALESCE(score1, 0) + COALESCE(score2, 0) + COALESCE(score3, 0) AS total_score FROM exam_scores;
You can see that in both cases, the function is used to replace any NULL values with a default value of 0 to ensure the calculation is accurate.
Dating Formatting when dealing with TEXT.
SELECT CONCAT(COALESCE(first_name, ''), ' ', COALESCE(last_name, '')) AS full_name FROM customers;
This query will return a list of customer full names, concatenating the first and last name columns. The COALESCE function is used to replace NULL values with empty strings to ensure proper string concatenation.