Checking NULL Values Using IS NULL and IS NOT NULL in MySQL – Detailed Notes

Course: MySQL Training

Topic: Checking NULL Values Using IS NULL and IS NOT NULL

Course Link: MySQL Training Course

Demo Link: Register for a Free Course Demo

---

1. Introduction to NULL Values in MySQL

In MySQL, NULL represents a missing, unknown, or unavailable value in a database column. It is used when a value has not been provided, is not known, or is not applicable to a particular record.

For example, an employee table may contain an employee whose phone number has not been provided. A customer table may have a missing email address, or an order record may not yet have a delivery date.

MySQL provides two important operators for checking NULL values:

• IS NULL: Identifies records where a column contains NULL.
• IS NOT NULL: Identifies records where a column contains a value other than NULL.

These operators are frequently used with the WHERE clause to filter incomplete records, identify missing information, validate data quality, and generate reports.

2. Learning Objectives

After completing this topic, learners will be able to:

• Understand the meaning of NULL in MySQL.
• Differentiate NULL from zero, an empty string, and ordinary text.
• Use IS NULL to find missing values.
• Use IS NOT NULL to find populated values.
• Combine NULL checks with AND, OR, and NOT.
• Use NULL checks with other comparison operators and filtering conditions.
• Understand how NULL affects SQL logical expressions.
• Identify missing data in employee, customer, order, and product tables.
• Update records containing NULL values.
• Apply best practices for NULL handling and database data quality.

3. What Is NULL in MySQL?

NULL is a special marker that indicates the absence of a known value. It does not represent a particular number or text value.

3.1 Examples of NULL Values

Column	Example Value	Meaning
phone_number	NULL	The phone number is missing or unknown.
email	NULL	The email address has not been provided or is unknown.
middle_name	NULL	The middle name is unavailable or not recorded.
delivery_date	NULL	The delivery date is not currently known or recorded.
manager_id	NULL	The manager relationship is not assigned or applicable.

The exact meaning of NULL depends on the application and business rules. It may mean unknown, missing, not yet available, or not applicable.

3.2 Difference Between NULL, Zero, and Empty String

NULL, zero, and an empty string are different values in MySQL.

Value	Example	Meaning
NULL	NULL	No known value is stored.
Zero	0	A numeric value equal to zero.
Empty string	''	A text value containing zero characters.
Space	' '	A text value containing a space character.
Ordinary text	'Unknown'	A text value containing the word Unknown.

These values should not be treated as interchangeable. A zero salary is a numeric value, while a NULL salary indicates that the salary value is missing or unknown. Similarly, an empty email string is different from a NULL email.

4. Understanding IS NULL

The IS NULL operator checks whether a column contains a NULL value. It is used in the WHERE clause to retrieve records where the specified column has no stored value.

4.1 Syntax of IS NULL

SELECT column1, column2, ...
FROM table_name
WHERE column_name IS NULL;

Explanation:

• SELECT specifies which columns should appear in the result.
• FROM identifies the table being queried.
• WHERE filters the records.
• IS NULL selects records where the specified column contains NULL.

4.2 Simple Example

SELECT employee_name, phone_number
FROM employees
WHERE phone_number IS NULL;

This query returns employees whose phone number is missing or has not been recorded.

4.3 Why IS NULL Is Important

IS NULL is useful when a business needs to identify incomplete records, follow up on missing information, or find records that need additional processing.

• Find customers without email addresses.
• Identify employees without assigned managers.
• Find products without descriptions.
• Identify orders without delivery dates.
• Find records with missing registration information.

5. Understanding IS NOT NULL

The IS NOT NULL operator checks whether a column contains a value other than NULL. It selects records where the specified column has a known or stored value.

5.1 Syntax of IS NOT NULL

SELECT column1, column2, ...
FROM table_name
WHERE column_name IS NOT NULL;

5.2 Simple Example

SELECT employee_name, phone_number
FROM employees
WHERE phone_number IS NOT NULL;

This query returns employees whose phone number column is populated with a non-NULL value.

5.3 Why IS NOT NULL Is Important

IS NOT NULL helps retrieve records that have the information required for a task or report.

• Find customers who have an email address recorded.
• Identify employees who have an assigned manager.
• Retrieve products with descriptions.
• Find orders with recorded delivery dates.
• Select records with known registration or payment information.

6. Creating a Sample Database

To understand IS NULL and IS NOT NULL, we will create a sample employee table containing both NULL and non-NULL values.

6.1 Creating the Database

CREATE DATABASE company_db;
USE company_db;

6.2 Creating the Employees Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department VARCHAR(50),
    city VARCHAR(50),
    phone_number VARCHAR(20),
    email VARCHAR(100),
    salary DECIMAL(10,2),
    manager_id INT
);

The table includes phone number, email, and manager ID columns that can contain NULL values.

6.3 Inserting Sample Records

INSERT INTO employees
(employee_id, employee_name, department, city, phone_number, email, salary, manager_id)
VALUES
(1, 'Amit Sharma', 'IT', 'Mumbai', '9876543210', '[email protected]', 65000, 5),
(2, 'Priya Verma', 'HR', 'Pune', NULL, '[email protected]', 48000, 7),
(3, 'Rahul Mehta', 'IT', 'Delhi', '9123456780', NULL, 75000, 5),
(4, 'Sneha Patil', 'Finance', 'Mumbai', NULL, NULL, 55000, NULL),
(5, 'Vikas Singh', 'IT', 'Pune', '9988776655', '[email protected]', 85000, NULL),
(6, 'Neha Joshi', 'IT', 'Mumbai', '9012345678', '[email protected]', 52000, 5),
(7, 'Karan Shah', 'HR', 'Delhi', NULL, '[email protected]', 60000, NULL),
(8, 'Pooja Desai', 'Finance', 'Pune', '9090909090', NULL, 70000, 5),
(9, 'Rohan Kulkarni', 'Sales', 'Mumbai', '9898989898', '[email protected]', 45000, 7),
(10, 'Ananya Rao', 'IT', 'Pune', NULL, '[email protected]', 80000, 5);

6.4 Displaying All Records

SELECT * FROM employees;

This query displays all employee records, including those with missing phone numbers, email addresses, or manager IDs.

7. Finding Records with NULL Values Using IS NULL

7.1 Find Employees Without Phone Numbers

SELECT employee_id, employee_name, phone_number
FROM employees
WHERE phone_number IS NULL;

This query returns employees whose phone number is not recorded. In the sample data, these include Priya Verma, Sneha Patil, Karan Shah, and Ananya Rao.

7.2 Find Employees Without Email Addresses

SELECT employee_id, employee_name, email
FROM employees
WHERE email IS NULL;

This query identifies employees whose email address is missing.

7.3 Find Employees Without an Assigned Manager

SELECT employee_id, employee_name, manager_id
FROM employees
WHERE manager_id IS NULL;

This query retrieves employees whose manager ID is not assigned. Depending on the organization's structure, this may include senior employees or employees whose reporting relationships have not been recorded.

7.4 Find Employees Without a Department

SELECT employee_id, employee_name, department
FROM employees
WHERE department IS NULL;

This query identifies employees whose department information is missing.

7.5 Find Employees Without a City

SELECT employee_id, employee_name, city
FROM employees
WHERE city IS NULL;

This query finds employee records without a recorded city.

8. Finding Records with Values Using IS NOT NULL

8.1 Find Employees with Phone Numbers

SELECT employee_id, employee_name, phone_number
FROM employees
WHERE phone_number IS NOT NULL;

This query returns employees whose phone number field contains a non-NULL value.

8.2 Find Employees with Email Addresses

SELECT employee_id, employee_name, email
FROM employees
WHERE email IS NOT NULL;

This query returns employees who have an email value recorded. It does not guarantee that the email address is valid or deliverable; it only checks that the column is not NULL.

8.3 Find Employees with an Assigned Manager

SELECT employee_id, employee_name, manager_id
FROM employees
WHERE manager_id IS NOT NULL;

This query retrieves employees whose manager ID is recorded.

8.4 Find Employees with a Department

SELECT employee_id, employee_name, department
FROM employees
WHERE department IS NOT NULL;

This query excludes employees whose department is missing.

8.5 Find Employees with a Recorded City

SELECT employee_id, employee_name, city
FROM employees
WHERE city IS NOT NULL;

This query returns employees whose city field contains a non-NULL value.

9. Difference Between IS NULL and IS NOT NULL

Feature	IS NULL	IS NOT NULL
Purpose	Finds records with missing or unknown values.	Finds records with non-NULL values.
Condition result	TRUE when the column is NULL.	TRUE when the column is not NULL.
Example	email IS NULL	email IS NOT NULL
Common use	Identify incomplete records.	Retrieve records with recorded information.
Empty string	Does not match an empty string.	Matches an empty string because it is not NULL.
Zero	Does not match zero.	Matches zero because zero is not NULL.

10. Why We Cannot Use = NULL or != NULL

In SQL, NULL represents an unknown or missing value. Comparisons such as = NULL and <> NULL do not evaluate to TRUE or FALSE in the ordinary way. They generally produce UNKNOWN.

10.1 Incorrect: Checking for NULL with Equality

SELECT employee_name, email
FROM employees
WHERE email = NULL;

This query does not correctly find rows with missing email values because the comparison evaluates to UNKNOWN.

10.2 Correct: Using IS NULL

SELECT employee_name, email
FROM employees
WHERE email IS NULL;

10.3 Incorrect: Checking for Non-NULL with Not Equal

SELECT employee_name, email
FROM employees
WHERE email != NULL;

This query does not correctly retrieve all rows with non-NULL email values.

10.4 Correct: Using IS NOT NULL

SELECT employee_name, email
FROM employees
WHERE email IS NOT NULL;

Important rule: Use IS NULL and IS NOT NULL whenever you need to test for the presence or absence of NULL values.

11. Understanding Three-Valued Logic in MySQL

SQL conditions can evaluate to TRUE, FALSE, or UNKNOWN. UNKNOWN commonly occurs when a comparison involves NULL.

Expression	Result	Explanation
NULL = NULL	UNKNOWN	Equality does not establish that two unknown values are equal.
NULL = 10	UNKNOWN	The comparison value is unknown.
NULL <> 10	UNKNOWN	The comparison cannot establish that the values differ.
NULL IS NULL	TRUE	The expression explicitly checks for NULL.
NULL IS NOT NULL	FALSE	The expression checks for a non-NULL value.

The WHERE clause returns rows only when the condition evaluates to TRUE. Rows for which the condition is FALSE or UNKNOWN are not returned.

12. Combining IS NULL with AND

The AND operator can combine a NULL check with additional requirements. Every condition must be true for a record to be selected.

12.1 Example: Employees Without Email in the IT Department

SELECT employee_name, department, email
FROM employees
WHERE email IS NULL
  AND department = 'IT';

This query identifies IT employees whose email addresses are missing.

12.2 Example: Employees Without Phone Numbers Who Earn More Than ₹50,000

SELECT employee_name, phone_number, salary
FROM employees
WHERE phone_number IS NULL
  AND salary > 50000;

This query returns employees whose phone number is missing and whose salary exceeds ₹50,000.

12.3 Example: Employees with a Missing Manager in a Selected City

SELECT employee_name, city, manager_id
FROM employees
WHERE manager_id IS NULL
  AND city = 'Pune';

This query finds employees in Pune who do not have a manager ID recorded.

13. Combining IS NULL with OR

The OR operator is useful when a record should be selected if one or more conditions are true.

13.1 Example: Employees Missing Phone Numbers or Email Addresses

SELECT employee_name, phone_number, email
FROM employees
WHERE phone_number IS NULL
   OR email IS NULL;

This query returns employees who are missing a phone number, an email address, or both.

13.2 Example: Employees Without a Manager or Department

SELECT employee_name, manager_id, department
FROM employees
WHERE manager_id IS NULL
   OR department IS NULL;

This query identifies employees who have no recorded manager or whose department is missing.

13.3 Example: Customers Missing Contact Details

Assume a table named customers contains the columns customer_name, phone_number, and email.

SELECT customer_name, phone_number, email
FROM customers
WHERE phone_number IS NULL
   OR email IS NULL;

This query identifies customers who need follow-up because at least one contact field is missing.

14. Combining IS NOT NULL with AND

IS NOT NULL can be combined with AND to require both a recorded value and another condition.

14.1 Example: Employees with an Email and Active Status

SELECT employee_name, email
FROM employees
WHERE email IS NOT NULL
  AND department = 'IT';

This query retrieves IT employees whose email field is populated.

14.2 Example: Employees with a Recorded Phone Number and Salary Above ₹50,000

SELECT employee_name, phone_number, salary
FROM employees
WHERE phone_number IS NOT NULL
  AND salary > 50000;

This query returns employees whose phone number is recorded and whose salary exceeds ₹50,000.

14.3 Example: Employees with a Manager and at Least Three Years of Experience

SELECT employee_name, manager_id, experience
FROM employees
WHERE manager_id IS NOT NULL
  AND experience >= 3;

This query retrieves employees with an assigned manager and at least three years of experience.

15. Combining IS NOT NULL with OR

OR can be used when a record qualifies if any one of several columns has a recorded value.

15.1 Example: Employees with Either an Email or a Phone Number

SELECT employee_name, phone_number, email
FROM employees
WHERE phone_number IS NOT NULL
   OR email IS NOT NULL;

This query returns employees who have at least one of the two contact fields populated.

15.2 Example: Employees with a Manager or an Email Address

SELECT employee_name, manager_id, email
FROM employees
WHERE manager_id IS NOT NULL
   OR email IS NOT NULL;

This query retrieves employees who have a manager ID recorded or an email address recorded.

16. Using NOT with NULL Checks

The NOT operator negates a condition. For NULL checks, NOT can be used to reverse IS NULL, although IS NOT NULL is usually clearer.

16.1 NOT IS NULL

SELECT employee_name, email
FROM employees
WHERE NOT (email IS NULL);

This query returns employees whose email is not NULL.

16.2 Equivalent IS NOT NULL Form

SELECT employee_name, email
FROM employees
WHERE email IS NOT NULL;

The second form is easier to read and is the recommended approach for routine NULL checks.

16.3 Excluding Records with Missing Contact Information

SELECT employee_name, phone_number, email
FROM employees
WHERE phone_number IS NOT NULL
  AND email IS NOT NULL;

This query returns employees whose phone number and email fields are both non-NULL.

17. Checking NULL Values with Other Conditions

NULL checks can be combined with comparison operators, IN, NOT IN, LIKE, and BETWEEN to create more specific queries.

17.1 NULL Check with IN

SELECT employee_name, department, email
FROM employees
WHERE department IN ('IT', 'Finance')
  AND email IS NULL;

This query finds employees in IT or Finance whose email address is missing.

17.2 NULL Check with NOT IN

SELECT employee_name, department, phone_number
FROM employees
WHERE department NOT IN ('HR', 'Sales')
  AND phone_number IS NULL;

This query retrieves employees outside HR and Sales whose phone number is missing. Employees with a NULL department are not included because NOT IN does not evaluate to TRUE for a NULL department.

17.3 NULL Check with LIKE

SELECT employee_name, email
FROM employees
WHERE email IS NOT NULL
  AND email LIKE '%@example.com';

This query returns employees whose email is recorded and ends with the specified text pattern. It checks the stored text pattern, not whether the address is valid or deliverable.

17.4 NULL Check with BETWEEN

SELECT employee_name, salary
FROM employees
WHERE salary IS NOT NULL
  AND salary BETWEEN 50000 AND 80000;

This query explicitly requires a non-NULL salary within the inclusive range of ₹50,000 to ₹80,000. The salary IS NOT NULL condition is logically redundant for this comparison but may make the intended data-quality requirement more obvious.

18. Difference Between NULL and Empty String

A common database issue occurs when missing text values are stored as empty strings instead of NULL. These values require different conditions.

18.1 Finding NULL Email Values

SELECT employee_name, email
FROM employees
WHERE email IS NULL;

18.2 Finding Empty Email Strings

SELECT employee_name, email
FROM employees
WHERE email = '';

The first query finds NULL email values. The second finds email values stored as empty strings.

18.3 Finding NULL or Empty Email Values

SELECT employee_name, email
FROM employees
WHERE email IS NULL
   OR email = '';

This query identifies both NULL email values and empty strings.

18.4 Finding NULL, Empty, or Whitespace-Only Values

SELECT employee_name, email
FROM employees
WHERE email IS NULL
   OR TRIM(email) = '';

The TRIM function removes leading and trailing spaces before checking whether the resulting string is empty. This is useful when a column may contain whitespace-only values.

Note: TRIM(email) = '' also evaluates as UNKNOWN when email is NULL, but the separate email IS NULL condition explicitly includes those rows.

19. Checking NULL Values in Multiple Columns

In many database applications, several columns may be optional. You may need to find records where multiple values are missing or where at least one required value is absent.

19.1 Find Employees Missing Both Phone and Email

SELECT employee_name, phone_number, email
FROM employees
WHERE phone_number IS NULL
  AND email IS NULL;

This query returns employees whose phone number and email address are both NULL.

19.2 Find Employees Missing Either Phone or Email

SELECT employee_name, phone_number, email
FROM employees
WHERE phone_number IS NULL
   OR email IS NULL;

This query returns employees who are missing at least one contact field.

19.3 Find Employees with Complete Contact Information

SELECT employee_name, phone_number, email
FROM employees
WHERE phone_number IS NOT NULL
  AND email IS NOT NULL;

This query selects employees whose phone number and email fields are both non-NULL.

19.4 Find Employees Missing Any One of Three Fields

SELECT employee_name, department, city, email
FROM employees
WHERE department IS NULL
   OR city IS NULL
   OR email IS NULL;

This query identifies employees with at least one missing value among department, city, and email.

20. Counting NULL and Non-NULL Values

MySQL aggregate functions can help measure data completeness. The COUNT(column_name) function counts non-NULL values, while COUNT(*) counts rows.

20.1 Count All Employee Records

SELECT COUNT(*) AS total_employees
FROM employees;

This query counts every row in the employees table.

20.2 Count Employees with Email Addresses

SELECT COUNT(email) AS employees_with_email
FROM employees;

COUNT(email) counts only rows where email is not NULL.

20.3 Count Employees Without Email Addresses

SELECT COUNT(*) AS employees_without_email
FROM employees
WHERE email IS NULL;

This query counts rows whose email column is NULL.

20.4 Count Missing and Populated Values Together

SELECT
    COUNT(*) AS total_employees,
    COUNT(email) AS employees_with_email,
    SUM(email IS NULL) AS employees_without_email
FROM employees;

In MySQL, the expression email IS NULL evaluates to 1 when true and 0 when false, allowing SUM to count NULL values. This example assumes the table contains employee rows; the aggregate result still returns one row when the table is empty.

20.5 Calculate Email Completeness Percentage

SELECT
    COUNT(*) AS total_employees,
    COUNT(email) AS employees_with_email,
    ROUND(
        100.0 * COUNT(email) / NULLIF(COUNT(*), 0),
        2
    ) AS email_completeness_percentage
FROM employees;

This query calculates the percentage of employee rows with non-NULL email values. NULLIF prevents division by zero if the table has no records.

21. Using IFNULL and COALESCE

IS NULL and IS NOT NULL are used to test for missing values. MySQL also provides functions such as IFNULL and COALESCE to substitute a fallback value when an expression is NULL.

21.1 Using IFNULL

SELECT
    employee_name,
    IFNULL(email, 'Email Not Available') AS email_display
FROM employees;

IFNULL returns the email value when it is non-NULL. Otherwise, it returns the text 'Email Not Available'.

21.2 Using COALESCE

SELECT
    employee_name,
    COALESCE(email, phone_number, 'No Contact Information') AS contact_details
FROM employees;

COALESCE returns the first non-NULL value from its arguments. In this example, it displays the email if available, otherwise the phone number, and otherwise a fallback message.

21.3 Difference Between NULL Checks and Replacement Functions

Feature	IS NULL / IS NOT NULL	IFNULL / COALESCE
Purpose	Tests whether a value is NULL.	Returns a fallback value when an expression is NULL.
Typical use	Filtering records.	Displaying or calculating values with a fallback.
Example	email IS NULL	IFNULL(email, 'Missing')

22. Updating NULL Values

Sometimes missing values need to be corrected after the proper information becomes available. MySQL allows records to be updated by using IS NULL in the WHERE clause.

22.1 Update a Missing Department

UPDATE employees
SET department = 'IT'
WHERE employee_id = 12
  AND department IS NULL;

This statement assigns IT to employee 12 only if the department value is currently NULL. Use the appropriate department only when it has been verified.

22.2 Fill a Default Value for Missing Status

Assume a table named products has a nullable status column.

UPDATE products
SET status = 'Pending Review'
WHERE status IS NULL;

This statement assigns Pending Review to all products whose status is NULL. Before running an update on many rows, review the affected records with SELECT.

22.3 Preview Records Before Updating

SELECT employee_id, employee_name, department
FROM employees
WHERE department IS NULL;

Inspect the results before running an UPDATE. Do not replace NULL values with arbitrary data merely to make a report look complete.

23. Deleting Records with NULL Values

IS NULL can be used in a DELETE statement to remove records with missing values. However, deleting records should be done only when the business requirement explicitly permits it.

23.1 Preview Records to Be Deleted

SELECT employee_id, employee_name, email
FROM employees
WHERE email IS NULL;

23.2 Delete Records with Missing Email Addresses

DELETE FROM employees
WHERE email IS NULL;

This statement permanently deletes every employee record whose email is NULL, unless the operation is rolled back within a supported transaction before it is committed. Do not run it simply because an email is missing; the records may still be valuable.

23.3 Safer Approach Using a Transaction

START TRANSACTION;

SELECT employee_id, employee_name, email
FROM employees
WHERE email IS NULL;

DELETE FROM employees
WHERE email IS NULL;

-- Verify the result before deciding to commit.
SELECT COUNT(*) AS remaining_missing_email
FROM employees
WHERE email IS NULL;

-- Use COMMIT only if the result is correct.
-- Otherwise, use ROLLBACK before committing.

Transaction behavior depends on the storage engine and session configuration. For production data, take appropriate backups and confirm the target rows before performing destructive operations.

24. NULL Values and Aggregate Functions

Many MySQL aggregate functions ignore NULL values in the column being aggregated. Understanding this behavior is important when calculating totals, averages, and counts.

24.1 COUNT(*) Versus COUNT(column)

SELECT
    COUNT(*) AS total_rows,
    COUNT(email) AS non_null_email_count
FROM employees;

COUNT(*) counts all rows, whereas COUNT(email) counts only non-NULL email values.

24.2 AVG and NULL Values

SELECT AVG(salary) AS average_salary
FROM employees;

AVG(salary) ignores rows where salary is NULL. If the salary column is NULL for every row, the result is NULL.

24.3 Finding Employees Without Recorded Salaries

SELECT employee_id, employee_name
FROM employees
WHERE salary IS NULL;

This query identifies employees whose salary is missing. A missing salary should not be interpreted as a salary of zero.

25. Common Mistakes and Their Solutions

25.1 Using Equality to Check NULL

Incorrect:

SELECT *
FROM employees
WHERE email = NULL;

Correct:

SELECT *
FROM employees
WHERE email IS NULL;

25.2 Using Not-Equal to Find Non-NULL Values

Incorrect:

SELECT *
FROM employees
WHERE email != NULL;

Correct:

SELECT *
FROM employees
WHERE email IS NOT NULL;

25.3 Confusing Empty Strings with NULL

Incorrect if the goal is to find both NULL and empty values:

SELECT *
FROM employees
WHERE email IS NULL;

This query does not find empty strings.

Correct when both types of missing text should be found:

SELECT *
FROM employees
WHERE email IS NULL
   OR email = '';

25.4 Forgetting That NULL Can Affect AND and OR

When a condition involves NULL, its result may be UNKNOWN. For example, email = '[email protected]' is UNKNOWN when email is NULL. Review the complete logical expression instead of assuming every condition is simply TRUE or FALSE.

25.5 Assuming IS NOT NULL Means the Data Is Valid

IS NOT NULL checks only that the value is not NULL. It does not verify that an email address is correctly formatted, a phone number is reachable, or a date is logically valid.

25.6 Updating or Deleting Too Many Records

Always use a SELECT query with the same WHERE condition to preview the affected records before running an UPDATE or DELETE statement.

26. Best Practices for Handling NULL Values

• Use IS NULL to find missing values.
• Use IS NOT NULL to find populated values.
• Never rely on = NULL or <> NULL for NULL checks.
• Understand the difference between NULL, zero, empty strings, and spaces.
• Use AND when all conditions must be satisfied and OR when any condition can be satisfied.
• Use parentheses to make complex conditions easier to understand.
• Validate data before replacing NULL values with defaults.
• Do not assume that every NULL value is an error; it may be valid for the business process.
• Use COUNT(column) to count non-NULL values and COUNT(*) to count rows.
• Use IFNULL or COALESCE when a fallback value is required for display or calculation.
• Preview rows before executing UPDATE or DELETE statements.
• Use transactions and backups when modifying important data.
• Define columns as NOT NULL when the application requires a value and the data model supports that rule.
• Use suitable validation rules to reduce unwanted missing values at the point of data entry.
• Document the business meaning of nullable columns.

27. Practical Business Examples

27.1 Customer Management

Requirement: Find customers without an email address.

SELECT customer_id, customer_name, email
FROM customers
WHERE email IS NULL;

27.2 E-Commerce Product Management

Requirement: Find products without a description.

SELECT product_id, product_name, description
FROM products
WHERE description IS NULL;

27.3 Order Management

Requirement: Find orders without a delivery date.

SELECT order_id, customer_id, delivery_date
FROM orders
WHERE delivery_date IS NULL;

A NULL delivery date may indicate that the order has not been delivered yet or that the date has not been recorded. The application should define which meaning applies.

27.4 Student Management

Requirement: Find students whose final marks have not been entered.

SELECT student_id, student_name, final_marks
FROM students
WHERE final_marks IS NULL;

27.5 Employee Management

Requirement: Find employees with complete contact information.

SELECT employee_id, employee_name, phone_number, email
FROM employees
WHERE phone_number IS NOT NULL
  AND email IS NOT NULL;

27.6 Sales Reporting

Requirement: Find sales transactions without a recorded payment date.

SELECT transaction_id, customer_id, payment_date
FROM transactions
WHERE payment_date IS NULL;

28. Step-by-Step Method for Checking NULL Values

1. Identify the table: Determine which database table contains the data.
2. Identify the column: Select the field that may contain missing values.
3. Define the requirement: Decide whether you need missing values or populated values.
4. Choose the operator: Use IS NULL for missing values or IS NOT NULL for non-NULL values.
5. Add extra conditions: Combine the NULL check with AND or OR when necessary.
6. Consider empty strings: Decide whether empty or whitespace-only text values should also count as missing.
7. Run a SELECT query: Inspect the records returned by the condition.
8. Validate the results: Confirm that the records match the intended business rule.
9. Modify data carefully: If updating or deleting records, preview the target rows and use appropriate safeguards.

29. Practice Exercises

Use the sample employees table to complete the following exercises.

1. Display employees whose phone number is NULL.
2. Display employees whose email address is not NULL.
3. Display employees who do not have an assigned manager.
4. Display employees whose department is NULL.
5. Display employees whose phone number and email are both NULL.
6. Display employees whose phone number or email is NULL.
7. Display employees whose email is not NULL and whose department is IT.
8. Display employees who have a manager ID and earn more than ₹50,000.
9. Count employees whose email address is NULL.
10. Count all employees and employees with recorded email addresses.
11. Display employees whose email is NULL or an empty string.
12. Display employees with complete phone and email information.

30. Practice Exercise Solutions

Exercise 1: Employees Without Phone Numbers

SELECT employee_name, phone_number
FROM employees
WHERE phone_number IS NULL;

Exercise 2: Employees with Email Addresses

SELECT employee_name, email
FROM employees
WHERE email IS NOT NULL;

Exercise 3: Employees Without Assigned Managers

SELECT employee_name, manager_id
FROM employees
WHERE manager_id IS NULL;

Exercise 4: Employees Without Departments

SELECT employee_name, department
FROM employees
WHERE department IS NULL;

Exercise 5: Employees Missing Both Phone and Email

SELECT employee_name, phone_number, email
FROM employees
WHERE phone_number IS NULL
  AND email IS NULL;

Exercise 6: Employees Missing Either Phone or Email

SELECT employee_name, phone_number, email
FROM employees
WHERE phone_number IS NULL
   OR email IS NULL;

Exercise 7: IT Employees with Email Addresses

SELECT employee_name, department, email
FROM employees
WHERE email IS NOT NULL
  AND department = 'IT';

Exercise 8: Employees with a Manager and Salary Above ₹50,000

SELECT employee_name, manager_id, salary
FROM employees
WHERE manager_id IS NOT NULL
  AND salary > 50000;

Exercise 9: Count Employees Without Email Addresses

SELECT COUNT(*) AS employees_without_email
FROM employees
WHERE email IS NULL;

Exercise 10: Count All Employees and Those with Emails

SELECT
    COUNT(*) AS total_employees,
    COUNT(email) AS employees_with_email
FROM employees;

Exercise 11: Emails That Are NULL or Empty

SELECT employee_name, email
FROM employees
WHERE email IS NULL
   OR email = '';

Exercise 12: Employees with Complete Contact Information

SELECT employee_name, phone_number, email
FROM employees
WHERE phone_number IS NOT NULL
  AND email IS NOT NULL;

31. Mini Project: Employee Data Quality Report

Project Objective

Create a MySQL report that identifies missing employee information and measures the completeness of important fields.

Project Requirements

• Count the total number of employee records.
• Count employees with missing phone numbers.
• Count employees with missing email addresses.
• Count employees without an assigned manager.
• Find employees missing both phone and email information.
• Calculate the percentage of employees with recorded email addresses.
• Display employees who have complete contact information.
• Identify empty strings separately from NULL values.

Sample Project Query

SELECT
    COUNT(*) AS total_employees,
    SUM(phone_number IS NULL) AS missing_phone_count,
    SUM(email IS NULL) AS missing_email_count,
    SUM(manager_id IS NULL) AS missing_manager_count,
    SUM(phone_number IS NOT NULL AND email IS NOT NULL)
        AS complete_contact_count,
    ROUND(
        100.0 * COUNT(email) / NULLIF(COUNT(*), 0),
        2
    ) AS email_completeness_percentage
FROM employees;

This report provides an overview of missing information and contact-data completeness. It can help a company decide which records need verification or follow-up.

32. Interview Questions and Answers

Q1. What is NULL in MySQL?

NULL represents a missing, unknown, or unavailable value in a database column.

Q2. What is the IS NULL operator?

IS NULL checks whether a column contains a NULL value.

Q3. What is the IS NOT NULL operator?

IS NOT NULL checks whether a column contains a non-NULL value.

Q4. What is the difference between NULL and zero?

NULL indicates the absence of a known value, while zero is a numeric value equal to 0.

Q5. What is the difference between NULL and an empty string?

NULL indicates a missing or unknown value. An empty string is a valid text value containing zero characters.

Q6. Why does email = NULL not work as expected?

Comparisons with NULL evaluate to UNKNOWN rather than TRUE. Use IS NULL to find NULL values.

Q7. How can non-NULL values be selected?

Use WHERE column_name IS NOT NULL.

Q8. Can IS NULL be combined with AND and OR?

Yes. These operators can combine NULL checks with other conditions to create more specific queries.

Q9. How do you find records where either of two fields is missing?

Use OR, such as WHERE phone_number IS NULL OR email IS NULL.

Q10. How do you find records where both fields are missing?

Use AND, such as WHERE phone_number IS NULL AND email IS NULL.

Q11. What does COUNT(column_name) count?

COUNT(column_name) counts non-NULL values in the specified column.

Q12. What does COUNT(*) count?

COUNT(*) counts all rows, including rows with NULL values in one or more columns.

Q13. What is the purpose of IFNULL?

IFNULL returns a specified alternative value when its first argument is NULL.

Q14. What is COALESCE used for?

COALESCE returns the first non-NULL expression from a list of expressions.

Q15. Does IS NOT NULL guarantee that the value is valid?

No. It only confirms that the value is not NULL. Additional validation may be needed.

Q16. How can NULL values be updated?

Use an UPDATE statement with an IS NULL condition in the WHERE clause.

Q17. Can records with NULL values be deleted?

Yes, using DELETE with an IS NULL condition. However, preview the affected records and confirm that deletion is appropriate before executing the statement.

Q18. What happens when a comparison with NULL is used in WHERE?

The comparison generally evaluates to UNKNOWN, and the row is not returned unless the overall WHERE expression evaluates to TRUE.

33. Quick Revision Notes

• NULL represents a missing, unknown, or unavailable value.
• NULL is different from zero, an empty string, and a space.
• Use IS NULL to find missing values.
• Use IS NOT NULL to find non-NULL values.
• Do not use = NULL or != NULL for NULL checks.
• Use AND when every condition must be true.
• Use OR when at least one condition can be true.
• Use IS NULL or IS NOT NULL with other filters when required.
• COUNT(column) ignores NULL values; COUNT(*) counts rows.
• Use IFNULL and COALESCE to provide fallback values.
• Check empty strings separately when necessary.
• Preview records before updating or deleting data.
• NULL checks identify missing values but do not validate data quality by themselves.

34. Final Checklist

• Can you explain what NULL means in MySQL?
• Can you differentiate NULL from zero and an empty string?
• Can you write queries using IS NULL and IS NOT NULL?
• Can you combine NULL checks with AND and OR?
• Can you count missing and populated values?
• Can you use IFNULL and COALESCE appropriately?
• Can you identify NULL and empty strings separately?
• Can you update NULL values safely?
• Can you preview records before deleting data?
• Can you explain why ordinary comparisons with NULL do not work as expected?

35. Conclusion

IS NULL and IS NOT NULL are essential MySQL operators for checking whether database columns contain missing values or recorded information. They help developers identify incomplete records, maintain data quality, generate accurate reports, and filter data according to business requirements.

Understanding the difference between NULL, zero, and empty strings is important for writing correct queries. Learners should also understand how NULL interacts with logical operators and aggregate functions, and how to handle missing values safely when updating or deleting records.

With regular practice, these operators can be applied to employee management, customer records, product catalogs, student databases, order processing, and data-quality reporting.

To learn more about SQL and database management, visit the MySQL Training Course. You can also register for a course demo to explore the training program.