Checking for Missing Data (IS NULL and IS NOT NULL)

What is a NULL Value?

In SQL, a NULL value represents missing or unknown data. It's a placeholder, not a value itself. It is crucial to understand that NULL is different from a zero (0) or an empty text string ('').

• A 0 in a failed_logins column means there have been zero failed logins.
• A NULL in the same column means the number of failed logins is unknown or was never recorded.

To get a clear understanding of this concept, a short video provides a helpful analogy.

The Correct Way to Test for NULL

Here is the most important rule about NULL: you cannot test for NULL values with standard comparison operators like = or <>. A condition like WHERE column_name = NULL will not work the way you might expect, because NULL is not considered equal to anything, not even another NULL.

Instead, SQL provides two specific operators for this purpose:

• IS NULL to find rows where a value is absent.
• IS NOT NULL to find rows where a value is present.

The following resource provides a concise summary of this rule and the syntax.

Practical Demonstration

Now let's see these operators in a live-coding context. The same video from earlier has excellent practical demonstrations that show IS NULL and IS NOT NULL being used in a WHERE clause.

To summarize, here are two visual examples.

Combining with Logical Operators

Just like the other conditions you've learned, IS NULL and IS NOT NULL can be combined with AND, OR, and NOT to build even more specific filters. This connects directly to what we covered in the last lesson.

Security Scenario: Imagine an accounts table. You might need to find all accounts that are currently marked as locked AND for which a last_login date has never been recorded. Such accounts might be targets for cleanup or investigation.

The query would look like this:

SELECT *
FROM accounts
WHERE account_status = 'locked' AND last_login IS NULL;

This is a powerful way to cross-reference different pieces of information to identify specific states or anomalies in your data.

Hands-On Practice

Let's apply these concepts to a few security-oriented tasks. For these exercises, assume we have an employee table with columns like name, salary, bonus, position, department_id, and manager_id.

Task 1: Identify Top-Level Accounts

• Objective: In many systems, top-level administrative or executive accounts do not report to anyone. Their manager_id would therefore be NULL. Write a query to find all employees who do not have a manager.
• Hint: You are looking for the absence of a value in the manager_id column.

SELECT *
FROM employee
WHERE manager_id IS NULL;

Task 2: Find Incomplete HR Records

• Objective: From a data integrity perspective, it's important to know which employee records are missing key information. Find all employees whose bonus amount has not been recorded (NULL) OR who have a recorded salary of less than 3000.
• Hint: This requires combining IS NULL with a standard comparison operator using OR. Remember the rules about parentheses from our last lesson if the logic gets more complex.

SELECT *
FROM employee
WHERE bonus IS NULL OR salary < 3000;

For more practice, the article "10 NULL Exercises for Beginners" on LearnSQL.com (resource LINK) provides several relevant problems (Exercises 1, 2, and 4 are particularly applicable to what we've covered).

Conclusion

In this lesson, you've added a crucial tool to your SQL toolkit for handling real-world data, which is often incomplete.

Key Takeaways:

• NULL represents the absence of data; it is not zero or an empty string.
• You must use IS NULL or IS NOT NULL to filter for NULL values. Standard operators like = will not work.
• These operators can be combined with AND, OR, and NOT to create powerful, specific queries for auditing and analysis.

In the next lesson, we will explore how to sort your query results using ORDER BY. You will also see that NULL values have a specific behavior during sorting, and you'll learn how to control where they appear in your output.