Notice: This page requires JavaScript to function properly.
Please enable JavaScript in your browser settings or update your browser.Learn CASE WHEN with NULL and Missing Data | Advanced CASE WHEN Logic
Mastering CASE WHEN in SQL

bookCASE WHEN with NULL and Missing Data

Handling NULL values in SQL queries is a common challenge, especially when working with real-world data that often contains missing or incomplete information. In SQL, a NULL value represents unknown or missing data. This can lead to unexpected results if not handled properly, since most operations involving NULL return NULL rather than a meaningful value. The CASE WHEN statement is a powerful tool for dealing with these situations, allowing you to substitute or flag NULL values directly in your query results.


              
1234567

            
SELECT
    name,
    CASE
        WHEN salary IS NULL THEN 'Unknown'
        ELSE CAST(salary AS VARCHAR)
    END AS salary_status
FROM employees;
copy

The query above demonstrates how you can use CASE WHEN in combination with the IS NULL condition to manage missing salary data. The expression salary IS NULL checks whether the salary field for each employee is missing. If it is, the query outputs the string 'Unknown'; otherwise, it converts the salary value to a string for display. The IS NULL condition is essential when working with CASE WHEN because direct comparisons like salary = NULL will not work as expectedβ€”IS NULL is the correct way to test for missing values.


              
12345678

            
SELECT
    name,
    department,
    CASE
        WHEN department IS NULL THEN 'Missing Department'
        ELSE 'Department Present'
    END AS department_flag
FROM employees;
copy

COALESCE function

The COALESCE function offers a streamlined way to handle NULL values in SQL. COALESCE returns the first non-NULL expression from its arguments, making it ideal for substituting default values when fields are missing. Instead of writing a CASE WHEN statement to check for NULL, you can often use COALESCE directly for a shorter, more readable query.

For example, the following two expressions produce the same result:

  • Using CASE WHEN: CASE WHEN salary IS NULL THEN 0 ELSE salary END;
  • Using COALESCE: COALESCE(salary, 0).

You can also combine COALESCE with CASE WHEN for more complex logic. This approach lets you provide multiple fallback options or apply different rules for handling missing data, while keeping your SQL concise and clear.


              
123456789

            
SELECT
    name,
    COALESCE(salary, 50000) AS salary_filled,
    CASE
        WHEN COALESCE(salary, 50000) >= 100000 THEN 'High'
        WHEN COALESCE(salary, 50000) >= 70000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_category
FROM employees;
copy

1. Why is it important to handle NULL values in SQL queries?

2. Fill in the blanks to create a CASE WHEN statement that outputs "No Data" if the department is NULL, otherwise shows the department name:

question mark

Why is it important to handle NULL values in SQL queries?

Select all correct answers

question-icon

Fill in the blanks to create a CASE WHEN statement that outputs "No Data" if the department is NULL, otherwise shows the department name:

SELECT name, department, CASE WHEN department THEN 'No Data' ELSE department END AS department_status FROM employees;
name | department | department_status
--------------+--------------------+-------------------
Alice Johnson | Engineering | Engineering
Bob Smith | Marketing | Marketing
Carol Lee | Human Resources | Human Resources
David Kim | Engineering | Engineering
Eva Brown | Sales | Sales
Frank White | Finance | Finance
Grace Green | Engineering | Engineering
Henry Black | Marketing | Marketing
Ivy Wilson | Sales | Sales
Jack Miller | Finance | Finance
Karen Davis | Human Resources | Human Resources
Leo Turner | Engineering | Engineering
Mona Scott | Sales | Sales
Nina Adams | Finance | Finance
Oscar Clark | Marketing | Marketing

Click or drag`n`drop items and fill in the blanks

Everything was clear?

How can we improve it?

Thanks for your feedback!

SectionΒ 2. ChapterΒ 4

Ask AI

expand

Ask AI

ChatGPT

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

bookCASE WHEN with NULL and Missing Data

Swipe to show menu

Handling NULL values in SQL queries is a common challenge, especially when working with real-world data that often contains missing or incomplete information. In SQL, a NULL value represents unknown or missing data. This can lead to unexpected results if not handled properly, since most operations involving NULL return NULL rather than a meaningful value. The CASE WHEN statement is a powerful tool for dealing with these situations, allowing you to substitute or flag NULL values directly in your query results.


              
1234567

            
SELECT
    name,
    CASE
        WHEN salary IS NULL THEN 'Unknown'
        ELSE CAST(salary AS VARCHAR)
    END AS salary_status
FROM employees;
copy

The query above demonstrates how you can use CASE WHEN in combination with the IS NULL condition to manage missing salary data. The expression salary IS NULL checks whether the salary field for each employee is missing. If it is, the query outputs the string 'Unknown'; otherwise, it converts the salary value to a string for display. The IS NULL condition is essential when working with CASE WHEN because direct comparisons like salary = NULL will not work as expectedβ€”IS NULL is the correct way to test for missing values.


              
12345678

            
SELECT
    name,
    department,
    CASE
        WHEN department IS NULL THEN 'Missing Department'
        ELSE 'Department Present'
    END AS department_flag
FROM employees;
copy

COALESCE function

The COALESCE function offers a streamlined way to handle NULL values in SQL. COALESCE returns the first non-NULL expression from its arguments, making it ideal for substituting default values when fields are missing. Instead of writing a CASE WHEN statement to check for NULL, you can often use COALESCE directly for a shorter, more readable query.

For example, the following two expressions produce the same result:

  • Using CASE WHEN: CASE WHEN salary IS NULL THEN 0 ELSE salary END;
  • Using COALESCE: COALESCE(salary, 0).

You can also combine COALESCE with CASE WHEN for more complex logic. This approach lets you provide multiple fallback options or apply different rules for handling missing data, while keeping your SQL concise and clear.


              
123456789

            
SELECT
    name,
    COALESCE(salary, 50000) AS salary_filled,
    CASE
        WHEN COALESCE(salary, 50000) >= 100000 THEN 'High'
        WHEN COALESCE(salary, 50000) >= 70000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_category
FROM employees;
copy

1. Why is it important to handle NULL values in SQL queries?

2. Fill in the blanks to create a CASE WHEN statement that outputs "No Data" if the department is NULL, otherwise shows the department name:

question mark

Why is it important to handle NULL values in SQL queries?

Select all correct answers

question-icon

Fill in the blanks to create a CASE WHEN statement that outputs "No Data" if the department is NULL, otherwise shows the department name:

SELECT name, department, CASE WHEN department THEN 'No Data' ELSE department END AS department_status FROM employees;
name | department | department_status
--------------+--------------------+-------------------
Alice Johnson | Engineering | Engineering
Bob Smith | Marketing | Marketing
Carol Lee | Human Resources | Human Resources
David Kim | Engineering | Engineering
Eva Brown | Sales | Sales
Frank White | Finance | Finance
Grace Green | Engineering | Engineering
Henry Black | Marketing | Marketing
Ivy Wilson | Sales | Sales
Jack Miller | Finance | Finance
Karen Davis | Human Resources | Human Resources
Leo Turner | Engineering | Engineering
Mona Scott | Sales | Sales
Nina Adams | Finance | Finance
Oscar Clark | Marketing | Marketing

Click or drag`n`drop items and fill in the blanks

Everything was clear?

How can we improve it?

Thanks for your feedback!

SectionΒ 2. ChapterΒ 4
some-alt