Learn Understanding Null Functions

Understanding how SQL handles NULL values is essential for writing reliable queries. In SQL, NULL represents missing or unknown data. Unlike zero or an empty string, NULL is a special marker that means the value does not exist. If you do not handle NULL values properly, you might get unexpected results when filtering, aggregating, or performing calculations. This is especially important in real-world databases, where missing information is common.


              123456789
            
-- Find employees who do not have a bonus assigned
SELECT name, department
FROM employees
WHERE bonus IS NULL;

-- Find employees who have a bonus assigned
SELECT name, bonus
FROM employees
WHERE bonus IS NOT NULL;

SQL provides several functions to work with NULL values effectively. The IFNULL function returns a specified value if the expression is NULL; otherwise, it returns the expression itself. The COALESCE function returns the first non-null value in a list of expressions, making it useful for providing fallback values. The NULLIF function compares two expressions and returns NULL if they are equal; otherwise, it returns the first expression. These functions help you manage missing data and prevent errors in your queries.


              123
            
-- Show each employee's bonus, using 1000.00 as a default if bonus is NULL
SELECT name, COALESCE(bonus, 1000.00) AS bonus
FROM employees;

1. Which function returns the first non-null value?

2. What does NULLIF do in a query?

3. Fill in the blanks to filter rows where bonus is NULL.

Everything was clear?

Thanks for your feedback!

Section 5. Chapter 1

Ask AI

Ask anything or try one of the suggested questions to begin our chat

Suggested prompts:

Can you explain the difference between NULL and an empty string in SQL?

How does the COALESCE function work with multiple columns?

Can you show more examples of handling NULL values in SQL queries?

Swipe to show menu