• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL NULL Values

← Previous Next →

In MySQL, NULL represents the absence of a value in a table column. It is not the same as an empty string ('') or zero (0). NULL is a special marker used when data is unknown, missing, or inapplicable.

Handling NULL values properly is essential because they can affect queries, calculations, and results. Many errors in SQL queries occur because NULL is misunderstood. For instance, comparing a column to NULL with = or != will not work as expected.

What is NULL?

  • NULL indicates no value exists in a column.

  • A column can be defined to allow or disallow NULL when creating a table:

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT,
    city VARCHAR(50) DEFAULT NULL
);

  • Here, name cannot be NULL, while age and city can be NULL.

  • NULL is different from an empty string or 0; it means data is absent.

Checking for NULL

To identify NULL values, MySQL provides IS NULL and IS NOT NULL:

SELECT * FROM students
WHERE city IS NULL;

  • Retrieves all students whose city is unknown.

SELECT * FROM students
WHERE city IS NOT NULL;

  • Retrieves all students with a defined city.

Note: You cannot use = or != to check for NULL. For example, city = NULL will not work.

Using NULL in Queries

Filtering with NULL

You can combine NULL checks with other conditions:

SELECT * FROM students
WHERE age > 18 AND city IS NOT NULL;

  • Retrieves students older than 18 who have a city defined.

Sorting with NULL

When using ORDER BY, NULL values are treated specially:

  • ASC (ascending) – NULLs appear first.

  • DESC (descending) – NULLs appear last.

Example:

SELECT name, city FROM students
ORDER BY city ASC;

Functions Handling NULL

MySQL provides functions to handle NULL values:

IFNULL()

Replaces NULL with a specified value:

SELECT name, IFNULL(city, 'Not Specified') AS city_name
FROM students;

  • If city is NULL, it displays 'Not Specified'.

COALESCE()

Returns the first non-NULL value from a list:

SELECT name, COALESCE(city, 'Unknown', 'NA') AS city_name
FROM students;

  • If city is NULL, it returns the first non-NULL value in the list.

NULLIF()

Returns NULL if two expressions are equal:

SELECT name, NULLIF(city, 'Delhi') AS city_check
FROM students;

  • If city is 'Delhi', the result is NULL; otherwise, it returns the city.

Aggregate Functions and NULL

When using aggregate functions, NULL values are usually ignored:

Function Behavior with NULL
COUNT(column) Ignores NULL values
COUNT(*) Counts all rows
SUM(column) Ignores NULL
AVG(column) Ignores NULL
MAX/MIN Ignores NULL

Example:

SELECT AVG(age) FROM students;

  • If some age values are NULL, MySQL calculates the average using only the non-NULL values.

Inserting NULL Values

You can insert NULL values explicitly:

INSERT INTO students (name, age, city)
VALUES ('Pooja', 19, NULL);

  • city is undefined for this student.

If a column allows NULL and you omit it in the INSERT statement, it defaults to NULL:

INSERT INTO students (name, age)
VALUES ('Riya', 20);

  • Here, city becomes NULL automatically.

Updating NULL Values

You can update a column to NULL:

UPDATE students
SET city = NULL
WHERE name = 'Meera';

  • Sets the city of Meera to unknown.

Best Practices

  1. Decide column NULL policy when creating tables: NOT NULL for mandatory fields, NULL for optional.

  2. Use IS NULL and IS NOT NULL for accurate queries.

  3. Handle NULL in calculations using functions like IFNULL or COALESCE.

  4. Be cautious with comparisons; NULL cannot be checked using = or !=.

  5. Consider default values for columns that may otherwise be NULL to simplify queries.

Real-Life Example

Suppose the students table contains:

id name age city
1 Aditi 19 Mumbai
2 Meera 20 NULL
3 Pooja 18 Pune
4 Riya 21 NULL
5 Ananya 22 Delhi

Example 1 – Find students without city:

SELECT * FROM students
WHERE city IS NULL;

Result: Meera, Riya

Example 2 – Replace NULL with default text:

SELECT name, IFNULL(city, 'Not Specified') AS city_name
FROM students;

Result:

  • Aditi → Mumbai

  • Meera → Not Specified

  • Pooja → Pune

  • Riya → Not Specified

  • Ananya → Delhi

Example 3 – Average age ignoring NULL:

SELECT AVG(age) AS avg_age FROM students;

  • NULL in age (if any) is ignored in calculation.

Summary of the Tutorial

  • NULL represents missing or unknown data.

  • Use IS NULL and IS NOT NULL to filter records.

  • Aggregate functions ignore NULL values by default.

  • Functions like IFNULL and COALESCE help handle NULL in queries.

  • Correct handling of NULL ensures accurate results, especially in reports, calculations, and data analysis.

Mastering NULL values is crucial for maintaining data integrity and avoiding unexpected results in MySQL queries.

Practice Questions

  1. Retrieve all students whose city is NULL.

  2. Select students whose city is NOT NULL.

  3. Insert a new student with city set to NULL.

  4. Update the city of a student to NULL.

  5. Use IFNULL to display ‘Not Specified’ for students with NULL city.

  6. Use COALESCE to display the first non-NULL value from city or a default value.

  7. Count the number of students whose city is NULL.

  8. Calculate the average age of students, ignoring NULL values.

  9. Select students where city is NULL and age is greater than 18.

  10. Insert a new student without specifying the city column and check that it defaults to NULL.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top