Table of Contents

1. Introduction
2. What is NULL in SQL?
3. NULL vs Zero vs Empty String
4. Why NULL Exists in Databases
5. Checking for NULL: IS NULL and IS NOT NULL
6. Using NULL in WHERE Clauses
7. NULL in SELECT Statements
8. Aggregating Data with NULL
9. Handling NULL in Comparisons
10. Using COALESCE() to Replace NULL
11. Using IFNULL() and NULLIF()
12. Using CASE to Deal with NULL
13. Sorting with NULL Values
14. Filtering with NOT IN and NULL
15. NULL in JOIN Conditions
16. NULL in GROUP BY and HAVING
17. Designing Tables with or without NULL
18. Common Mistakes with NULL
19. Best Practices for Working with NULL
20. Summary and What’s Next

1. Introduction

In SQL, handling missing or unknown data is critical. That’s where NULL comes in. Unlike 0 or '' (empty string), NULL means “no value” or “unknown value.” This module explains how SQL treats NULL, how it affects queries, and how to handle it cleanly.

2. What is NULL in SQL?

NULL represents the absence of a value. It is not a number, not a string, and not even a default value. It simply indicates nothing.

3. NULL vs Zero vs Empty String

They are not equal to each other in SQL.

4. Why NULL Exists in Databases

• Optional data (e.g., phone number, middle name)
• Missing values during data entry
• Future updates
• Unknown measurements or references

NULL allows databases to handle incomplete or pending data gracefully.

5. Checking for NULL: IS NULL and IS NOT NULL

You cannot use = or != to check for NULL.

Use:

sqlCopyEditSELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

6. Using NULL in WHERE Clauses

sqlCopyEditSELECT * FROM employees 
WHERE department IS NULL;

To find employees without assigned departments.

7. NULL in SELECT Statements

NULL appears in the result as blank or NULL, depending on the client.

sqlCopyEditSELECT name, phone FROM customers;

If phone is missing, the result will show a NULL.

8. Aggregating Data with NULL

Most aggregate functions ignore NULLs:

sqlCopyEditSELECT AVG(salary) FROM employees;  -- NULLs excluded
SELECT COUNT(*) FROM employees;     -- counts all rows
SELECT COUNT(salary) FROM employees; -- excludes NULL salaries

9. Handling NULL in Comparisons

sqlCopyEditSELECT * FROM products 
WHERE price != 0;  -- Excludes NULLs!

SELECT * FROM products 
WHERE price IS NULL OR price != 0;  -- Includes NULLs too

Any expression with NULL in it returns NULL (which is treated as false in conditions).

10. Using COALESCE() to Replace NULL

COALESCE() returns the first non-NULL value in a list:

sqlCopyEditSELECT name, COALESCE(phone, 'Not Provided') AS contact_number
FROM customers;

11. Using IFNULL() and NULLIF()

• IFNULL(col, default) → MySQL / SQLite
• NULLIF(val1, val2) → returns NULL if both values are equal

sqlCopyEditSELECT IFNULL(email, 'N/A') FROM users;
SELECT NULLIF(10, 10);  -- Returns NULL

12. Using CASE to Deal with NULL

sqlCopyEditSELECT name,
  CASE 
    WHEN phone IS NULL THEN 'Unknown'
    ELSE phone
  END AS phone_number
FROM customers;

You can customize NULL logic using CASE.

13. Sorting with NULL Values

Different DBMS handle NULL sorting differently:

• PostgreSQL:

sqlCopyEditORDER BY age DESC NULLS LAST;

• MySQL/SQLite: NULL usually appears first with ASC, last with DESC.

14. Filtering with NOT IN and NULL

Be cautious:

sqlCopyEditSELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);

If the subquery contains a NULL, it may return no results.

15. NULL in JOIN Conditions

Join results are affected when keys are NULL:

sqlCopyEditSELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

If no order exists, o.total will be NULL.

16. NULL in GROUP BY and HAVING

sqlCopyEditSELECT department, COUNT(*) 
FROM employees
GROUP BY department;

Rows with NULL department will be grouped together.

You can also filter groups with:

sqlCopyEditHAVING department IS NULL;

17. Designing Tables with or without NULL

sqlCopyEditCREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(15)  -- nullable by default
);

Use NOT NULL where values must always exist.

18. Common Mistakes with NULL

• Comparing with = NULL instead of IS NULL
• Forgetting NULLs in NOT IN subqueries
• Assuming NULL = 0 or ''
• Failing to account for NULLs in aggregations

19. Best Practices for Working with NULL

• Use NOT NULL constraints where data is mandatory
• Use COALESCE() or CASE to clean up query output
• Avoid NULL in foreign keys when possible
• Handle NULLs in sorting, aggregations, and filtering explicitly
• Document which columns may contain NULLs

20. Summary and What’s Next

NULL is a powerful feature of SQL that allows databases to handle incomplete or unknown information. However, if used without care, it can lead to incorrect or confusing results. Mastering how to work with NULL helps you write more accurate and resilient queries.