Welcome to our deep dive into SQL's IFNULL and COALESCE functions! These powerful tools help us handle null values in a database efficiently. Let's learn how to use them in a practical, beginner-friendly manner.
Before we jump into the functions, let's discuss what null values are. In SQL, a null value represents an unknown or missing value. Unlike other data types, comparing null values with anything (including another null) results in an unknown outcome.
SELECT NULL = NULL; -- Returns: falseBoth IFNULL and COALESCE serve the same purpose: to replace null values with a specified value. However, they have slightly different syntaxes.
IFNULL(expression1, expression2) returns expression1 if it's not null; otherwise, it returns expression2.
COALESCE(expression1, expression2, ..., expressionN) returns the first non-null expression. If all expressions are null, it returns null.
Now, let's look at some examples to understand these functions better.
Suppose we have a customers table with a phone_number column. Some rows might have null values. We can use IFNULL to replace those null values with a default number.
SELECT IFNULL(phone_number, '(000) 000-0000') as phone_number
FROM customers;In this example, we have a salaries table with salaries for multiple employees. Let's assume some employees don't have a bonus. We can use COALESCE to replace their null bonuses with a default value.
SELECT employee_id, salary, COALESCE(bonus, 0) as bonus
FROM salaries;What does the `IFNULL` function return if `expression1` is not null?
What does the `COALESCE` function return if all expressions are null?