Notice: This page requires JavaScript to function properly.
Please enable JavaScript in your browser settings or update your browser.Lära 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

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

Var allt tydligt?

Hur kan vi förbättra det?

Tack för dina kommentarer!

Avsnitt 2. Kapitel 4

Fråga AI

expand

Fråga AI

ChatGPT

Fråga vad du vill eller prova någon av de föreslagna frågorna för att starta vårt samtal

bookCASE WHEN with NULL and Missing Data

Svep för att visa menyn

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

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

Var allt tydligt?

Hur kan vi förbättra det?

Tack för dina kommentarer!

Avsnitt 2. Kapitel 4
some-alt