Filtering NULL Values with IS NULL in SQL

Many databases allow for missing data to be represented as NULL. Filtering rows where a specific column contains NULL values is a common task in data analysis and reporting. This challenge will test your understanding of how to use the IS NULL operator in SQL to identify and retrieve rows with NULL values in a specified column.

Problem Description

You are given a table named employees with the following columns: employee_id (INT), employee_name (VARCHAR), department (VARCHAR), and salary (DECIMAL). The salary column may contain NULL values, representing employees whose salary information is currently unavailable.

Your task is to write a SQL query that retrieves all rows from the employees table where the salary column is NULL. The query should return all columns of the employees table for these rows. Ensure your query correctly handles cases where the salary column contains actual zero values (0) and distinguishes them from NULL values. IS NULL is the correct operator to use for this purpose.

Key Requirements:

• The query must use the IS NULL operator to identify NULL values.
• The query must return all columns from the employees table.
• The query must correctly differentiate between NULL values and zero values in the salary column.
• The query must be valid SQL and executable against a standard SQL database.

Expected Behavior:

The query should return a result set containing only those rows from the employees table where the salary column has a NULL value. Rows where salary is 0 or any other non-NULL value should not be included in the result.

Edge Cases to Consider:

• The table might be empty. In this case, the query should return an empty result set.
• The salary column might contain only NULL values.
• The salary column might contain a mix of NULL, 0, and other non-NULL values.
• The table might contain a large number of rows.

Examples

Example 1:

Input:
employees table:
employee_id | employee_name | department | salary
------------|---------------|------------|--------
1           | Alice         | Sales      | 50000.00
2           | Bob           | Marketing  | NULL
3           | Charlie       | Sales      | 60000.00
4           | David         | IT         | NULL
5           | Eve           | HR         | 0.00

Output:
employee_id | employee_name | department | salary
------------|---------------|------------|--------
2           | Bob           | Marketing  | NULL
4           | David         | IT         | NULL

Explanation: The query should return only the rows where the salary is NULL. Alice and Charlie have non-NULL salaries, and Eve has a salary of 0, so they are excluded.

Example 2:

Input:
employees table:
employee_id | employee_name | department | salary
------------|---------------|------------|--------
1           | Alice         | Sales      | NULL
2           | Bob           | Marketing  | NULL
3           | Charlie       | Sales      | NULL

Output:
employee_id | employee_name | department | salary
------------|---------------|------------|--------
1           | Alice         | Sales      | NULL
2           | Bob           | Marketing  | NULL
3           | Charlie       | Sales      | NULL

Explanation: All salaries are NULL, so all rows are returned.

Example 3:

Input:
employees table:
employee_id | employee_name | department | salary
------------|---------------|------------|--------
1           | Alice         | Sales      | 50000.00
2           | Bob           | Marketing  | 0.00
3           | Charlie       | Sales      | 60000.00

Output:
(empty result set)

Explanation: No salaries are NULL, so an empty result set is returned.

Constraints

• The table employees always exists.
• The table employees will always have the columns employee_id, employee_name, department, and salary.
• The salary column is of type DECIMAL.
• The database system is a standard SQL database (e.g., MySQL, PostgreSQL, SQL Server).
• The query should be efficient enough to handle tables with up to 1,000,000 rows.

Notes

• Remember that NULL is not equal to 0. You cannot use = to compare with NULL.
• The IS NULL operator is specifically designed to check for NULL values.
• Consider the case where the table is empty. Your query should still execute without errors and return an empty result set.
• Focus on writing a clear and concise SQL query that accurately filters for NULL values in the salary column. Pseudocode:

SELECT *
FROM employees
WHERE salary IS NULL;