Leer CASE 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;

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;

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:

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;

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

Was alles duidelijk?

Bedankt voor je feedback!

Sectie 2. Hoofdstuk 4

Vraag AI

Vraag wat u wilt of probeer een van de voorgestelde vragen om onze chat te starten.

Veeg om het menu te tonen


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


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

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:

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;

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

Was alles duidelijk?

Bedankt voor je feedback!

Sectie 2. Hoofdstuk 4

CASE WHEN with NULL and Missing Data

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:

CASE WHEN with NULL and Missing Data

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:

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:

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: