DbSchema Database Designer

DbSchema Tutorial | SQL NULL FUNCTIONS

Publish on DbSchema Blog >>>

SQL, the backbone of many databases, often deals with data that may be absent or not yet available. Such data is represented by a special marker called NULL. This comprehensive guide is tailored to provide a deeper understanding of SQL’s NULL functions and their applications.

Introduction to NULL in SQL

In SQL, NULL symbolizes the absence or unavailability of data. It’s not equivalent to zero, an empty string, or any predetermined value. Instead, NULL uniquely marks that data is missing.

SQL NULL Functions: An Overview

SQL boasts a variety of functions to address NULL values. The most prominent among them are IFNULL(), ISNULL(), COALESCE(), and NVL(). Let’s dissect each.

1. IFNULL() Function


IFNULL(expression1, expression2)

IFNULL() checks the first expression. If it’s non-NULL, the function returns it; otherwise, it returns the second expression.


Consider the sample Students table:

ID Name Age Grade
1 Alice 20 NULL
2 Bob NULL B
3 Carol 22 C


SELECT Name, IFNULL(Age, "Unknown") AS Age FROM Students;


Name Age
Alice 20
Bob Unknown
Carol 22


For Bob, since the Age is NULL, the function returns “Unknown”.

2. ISNULL() Function


ISNULL(expression, replacement_value)

ISNULL() is akin to IFNULL(). It returns the first expression if it isn’t NULL; otherwise, it provides the replacement value.



SELECT Name, ISNULL(Age, "Not Specified") AS Age FROM Students;


Name Age
Alice 20
Bob Not Specified
Carol 22


Here, Bob’s age is NULL, so “Not Specified” is displayed.

3. COALESCE() Function


COALESCE(expression1, expression2, ...)

This function scans the list of expressions and returns the first non-NULL expression.



SELECT Name, COALESCE(Age, Grade, "Unknown") AS Detail FROM Students;


Name Detail
Alice 20
Bob B
Carol 22


For Bob, since the Age is NULL, the function moves to the next expression, Grade, and returns “B”.

4. NVL() Function


NVL(expression1, expression2)

Specific to Oracle databases, it returns the first expression if it’s non-NULL; else, it provides the second.

Counting NULL Values

To enumerate the NULL values in a column, combine COUNT() with IS NULL.







Only Bob has a NULL age, so the count is 1.

AVG() Function with NULL Values

When employing AVG(), SQL excludes NULL values, computing the average solely based on non-NULL entries.



SELECT AVG(Age) FROM Students;




Only Alice’s and Carol’s ages (20 and 22) are considered. Their average is 21.

Differentiating ‘IS NULL’ and ‘= NULL’

Criteria IS NULL = NULL
Usage Correct Incorrect
Represents Absence Comparison
SQL Standard Yes No

NULLs in String Concatenations

When concatenating a string with a NULL, the result is typically NULL.



SELECT Name, Age || ' years' AS AgeDetail FROM Students;


Name AgeDetail
Alice 20 years
Carol 22 years


Bob’s age is NULL, so the concatenated result is also NULL.

Common Pitfalls

  1. Equating NULL to empty strings or zero.
  2. Using = NULL instead of IS NULL.
  3. Neglecting that operations with NULL yield NULL.


  1. Is NULL a value?
    No, it signifies the absence of a value.
  2. Can NULL participate in mathematical operations?
    Yes, but the result remains NULL.
  3. How to default NULL to another value?
    Utilize IFNULL(), ISNULL(), COALESCE(), or NVL().

Practice Exercises

  1. Identify students with a NULL grade.
  2. Replace all NULL grades with “Not Assessed”.
  3. Compute the mean age, excluding NULL values.
  4. Merge the name and age of students, using “Unknown” for NULL ages.

Wrapping Up

Managing NULL values in SQL is paramount for precise data operations and analytics. By mastering these functions and methodologies, you’ll be adept at handling unavailable or missing data in SQL.

Remember: A NULL in SQL isn’t just a placeholder—it’s a significant marker of data that’s yet to be known.

Visually Manage Databases using DbSchema

DbSchema is a databases client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.
DbSchema main features include:

DbSchema Designer alt >

Interactive Diagrams

Design tables, column and foreign keys directly in diagrams, by double-clicking them. Changes will be saved to the design model and, if DbSchema is connected to the database also into the database. More.

Connection Dialog alt >

Simple Connection Dialog

Choose the database location, the user and password, and simply get connected. Choose 'Edit Manually' into the JDBC URL combo to enter a custom URL. More.

Relational Data Explorer alt >

Relational Data Explorer

Explore data from multiple tables simultaneously, using foreign keys or virtual foreign keys. Double-click cells to edit the data. More.

Query Builder alt >

Query Builder

Create SQL Queries featuring JOINS, GROUP BY, ORDER BY just using the mouse. More.

SQL Query Editor alt >

SQL Query Editor

Edit and execute SQL Queries. The editor is autocompletion-enabled. More.

Schema Synchronization alt >

Design Schema in Team & Schema Deployment

DbSchema is using the design model, a copy of the schema structure, independent of the database.
The design model can be saved to file and shared in a team.
Connecting to another database you may compare the design model with the database, commit the differences or merge them in the design model. More.

Dark Theme alt >

Dark Theme

Configurable styles & dark theme. More.

Many features are available in the free Community edition.
The Pro edition adds capabilities to save the design to the model file, design schema in team and deploy the schema on multiple databases.

DbSchema can be downloaded for free. No registration is required.