Previous
Written by: rJTutorial Team | Last Updated:

SQL IS NULL

In the world of databases, IS NULL is a specialized operator used to identify "missing" or "unknown" data. When a field in a table is optional and no value is provided during data entry, SQL marks that field as NULL.

It is important to understand that NULL is not a value it is a marker indicating the absence of a value. Because of this, you cannot use standard comparison operators like = or != to find them.

Developer Tip: Think of NULL as "I don't know what this is yet," whereas an empty string ("") is "I know this is empty." Distinguishing between the two is crucial for clean data architecture.

 

Basic Syntax of IS NULL

The syntax is straightforward. You place the IS NULL operator in your WHERE clause to filter out everything except the missing entries.

SELECT column1, column2, ...
FROM table_name
WHERE column_name IS NULL;
  • column_name: The specific field you want to check for missing data.
Common Mistake: Writing WHERE column_name = NULL. In SQL, nothing is equal to NULL (not even NULL itself). Using the equals sign will result in an empty set or an error, depending on your SQL flavor. Always use IS NULL.

 

Key Features of IS NULL

1. Check for NULL Values

The primary use of IS NULL is to find records that need attention or represent optional fields that haven't been filled yet. For example, finding employees who haven't been assigned to a department.

SELECT name  
FROM employees  
WHERE department IS NULL;
  • This query retrieves the names of all employees where the department field is currently empty or undefined.

2. Check for Non-NULL Values

Conversely, you often need to fetch only the records that are complete. By using IS NOT NULL, you can exclude any rows where data is missing.

SELECT name  
FROM employees  
WHERE department IS NOT NULL;
  • This is particularly useful for generating reports where you only want to see "active" or "assigned" records.
Best Practice: When designing your database schema, use the NOT NULL constraint on columns that are mandatory (like primary keys or email addresses) to prevent accidental NULL values from entering your system.

3. Using IS NULL with Multiple Conditions

Real-world queries often require checking for NULLs alongside other logic. You can use AND or OR to build complex filters.

SELECT name  
FROM employees  
WHERE department IS NULL  
   OR salary > 5000;
  • This query captures two groups of people: those without a department AND those who earn a high salary.

 

Example Queries

1. Find Rows with NULL Values

Imagine an e-commerce platform where users can checkout as guests. You might want to find orders that aren't linked to a registered account.

SELECT order_id, customer_id  
FROM orders  
WHERE customer_id IS NULL;
  • This helps identify "Guest" orders in the orders table.

2. Exclude Rows with NULL Values

If you are building a product catalog for your website, you probably don't want to show items that haven't had their price set yet.

SELECT product_id, product_name  
FROM products  
WHERE price IS NOT NULL;
  • This ensures that only products with a defined price are displayed to your customers.

3. Filter Rows Based on NULL and Non-NULL Conditions

Suppose you are running a marketing campaign. You want to find users who have provided a phone number but forgot to provide an email address.

SELECT id, email  
FROM users  
WHERE email IS NULL  
   AND phone_number IS NOT NULL;
  • This targets a specific subset of users for alternative contact methods.
Watch Out: Be careful when using NOT IN with subqueries that return NULLs. If a subquery returns even one NULL, the entire NOT IN condition will evaluate to "unknown," and your query may return zero results unexpectedly.

 

Important Notes

NULL is Not the Same as 0 or '' (Empty String):

  • A NULL value indicates that the data simply isn't there.
  • An integer 0 is a specific numerical value.
  • An empty string '' is a known text value with a length of zero.

NULL in Arithmetic Operations:

  • This is a major "gotcha": any mathematical operation involving a NULL results in NULL. If you add 10 to a NULL value, the result is NULL, not 10.
Developer Tip: Use functions like COALESCE(column, 0) or IFNULL() to provide a default value when a NULL is encountered during calculations.

Aggregate Functions and NULLs:

  • Most aggregate functions like SUM(), AVG(), and COUNT(column_name) automatically ignore NULL values. However, COUNT(*) counts every row, regardless of whether columns contain NULLs.

 

Summary

The IS NULL operator is an essential tool for any developer working with databases. It allows you to handle missing data gracefully and ensures your queries accurately reflect the state of your information. Remember that NULL represents an "unknown" state, and always use IS NOT NULL when you need to ensure data integrity in your results.

Previous