IS NULL Checks

Overview

IS NULL is the specialized operator used to check for the absence of data, handling SQL's unique three-valued logic.

Learning Objectives

• Explain why = NULL is logically incorrect in SQL.
• Retrieve missing or unpopulated fields using IS NULL and IS NOT NULL.
• Grasp how nulls behave during evaluations and logical operators.

Detailed Concept Explanation

In relational databases, NULL represents "unknown" or "missing" data.

Because NULL is not a concrete value, you cannot use equality operators like = to compare it. Any direct comparison with NULL (e.g., val = NULL or val != NULL) returns UNKNOWN (effectively FALSE for filter outcomes).

To check for null states, SQL provides the dedicated IS NULL and IS NOT NULL operators.

Code Examples

SQL

-- INCORRECT: This query compiles but returns zero rows
SELECT name FROM employees WHERE manager_id = NULL;

-- CORRECT: Use the dedicated IS NULL operator
SELECT name FROM employees WHERE manager_id IS NULL;

-- Retrieve active orders that have been shipped
SELECT order_id, ship_date 
FROM orders 
WHERE ship_date IS NOT NULL;

Best Practices

• Never compare with = NULL: Write automated lint checks or code reviews to catch logical bugs caused by = NULL checks.
• Default Constraints: Set NOT NULL constraints on columns during database definition to minimize handling edge cases.

Interview Perspective

Interactive Challenges

Summary

Always verify unpopulated database fields using IS NULL and IS NOT NULL. Avoid comparing nulls with equality operators.