• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL Null Functions

← Previous Next →

In MySQL, NULL represents a missing or undefined value in a table. It is different from 0 or an empty string. Working with NULLs requires special handling because standard operators like = or != do not work as expected with NULL.

MySQL provides several NULL functions to detect, replace, or handle NULL values in queries. These functions are essential for data integrity, reporting, and calculations.

IS NULL and IS NOT NULL

The simplest way to check for NULL values is using IS NULL or IS NOT NULL:

SELECT name, age
FROM students
WHERE age IS NULL;

  • Returns students whose age is unknown or not entered.

SELECT name, age
FROM students
WHERE age IS NOT NULL;

  • Returns students whose age is known.

Important Notes

  • = and != cannot be used with NULL.

  • Always use IS NULL or IS NOT NULL for proper checks.

IFNULL()

The IFNULL() function replaces NULL with a specified value. Syntax:

IFNULL(expression, replacement_value)

  • expression is the column or value to check.

  • replacement_value is returned if expression is NULL; otherwise, expression is returned.

Example

SELECT name, IFNULL(age, 18) AS Age
FROM students;

  • If a student’s age is NULL, it will display 18 instead.

  • Useful for default values in reports.

COALESCE()

The COALESCE() function returns the first non-NULL value from a list of expressions:

COALESCE(expression1, expression2, ..., expressionN)

Example

SELECT name, COALESCE(age, default_age, 18) AS Age
FROM students;

  • Returns age if not NULL, otherwise default_age, otherwise 18.

  • COALESCE is more flexible than IFNULL because it can handle multiple fallback values.

NULLIF()

The NULLIF() function returns NULL if two expressions are equal; otherwise, it returns the first expression. Syntax:

NULLIF(expression1, expression2)

Example

SELECT name, NULLIF(score, 0) AS Score
FROM students;

  • If score is 0, it returns NULL.

  • Useful for avoiding division by zero or marking specific values as missing.

Handling NULL in Aggregates

MySQL aggregate functions like SUM, AVG, COUNT, MAX, MIN handle NULL differently:

  • COUNT(column_name) ignores NULL values.

  • COUNT(*) counts all rows regardless of NULLs.

  • SUM(column_name) and AVG(column_name) ignore NULLs.

  • MAX and MIN skip NULLs.

Example

SELECT COUNT(age) AS AgeCount, AVG(age) AS AverageAge
FROM students;

  • AgeCount counts only non-NULL ages.

  • AverageAge is calculated ignoring NULL values.

Using NULL in Conditional Expressions

CASE with NULL

SELECT name,
       CASE
           WHEN age IS NULL THEN 'Unknown'
           ELSE 'Known'
       END AS AgeStatus
FROM students;

  • Classifies students based on whether their age is NULL or not.

IF() with NULL

SELECT name, IF(age IS NULL, 18, age) AS Age
FROM students;

  • Similar to IFNULL but uses conditional logic.

Practical Scenarios

  1. Default value for NULL age:

SELECT name, IFNULL(age, 18) AS Age
FROM students;

  1. First non-NULL value from multiple columns:

SELECT name, COALESCE(age, default_age, 18) AS Age
FROM students;

  1. Mark zero scores as NULL:

SELECT name, NULLIF(score, 0) AS Score
FROM students;

  1. Filter students with unknown age:

SELECT name
FROM students
WHERE age IS NULL;

  1. Count students with known ages:

SELECT COUNT(age) AS AgeCount
FROM students;

  • Ignores NULL values.

Important Points

  1. NULL is not equal to 0 or an empty string.

  2. Use IS NULL or IS NOT NULL to check for NULL.

  3. IFNULL() replaces NULL with a single fallback value.

  4. COALESCE() returns the first non-NULL value from a list.

  5. NULLIF() converts specific values to NULL.

  6. Aggregates like SUM, AVG, MAX, MIN ignore NULLs, while COUNT(*) counts all rows.

  7. Proper handling of NULLs prevents errors and incorrect calculations.

Summary of the Tutorial

  • NULL represents missing or undefined data.

  • Use IS NULL/IS NOT NULL to check for NULL values.

  • IFNULL() and COALESCE() replace NULLs with fallback values.

  • NULLIF() helps mark specific values as NULL.

  • Aggregates automatically ignore NULLs unless COUNT(*) is used.

  • Proper handling of NULL ensures accurate reporting, calculations, and data integrity.

Understanding MySQL null functions is essential for cleaning, transforming, and analyzing data, especially when dealing with incomplete or optional information.

Practice Questions

  1. Retrieve all students and display age as 18 if it is NULL using IFNULL().

  2. Retrieve student names and the first non-NULL value among age, default_age, or 18 using COALESCE().

  3. Retrieve scores but convert 0 values to NULL using NULLIF().

  4. Select students whose age is NULL.

  5. Select students whose age is NOT NULL.

  6. Count the number of students whose age is not NULL.

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

  8. Select students and display 'Unknown' for age if it is NULL using CASE.

  9. Select students and display age as is if not NULL, otherwise 18, using IF().

  10. Select total sum of scores, ensuring NULL scores are ignored in calculation.

← 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