Working with Null in SQL: A Beginner's Guide(12)

February 16, 2024

Null values are a common and tricky phenomenon in SQL databases. They represent the absence or unknown of any data in a column. In this blog post, we will learn how to handle null values in SQL queries using various techniques and functions. This is part of our SQL Learning series, where we cover the basics and advanced topics of SQL simply with examples. If you want to learn more about SQL, check out our other posts in the series and join our SQL learning community.

What is Null in SQL?

Before we dive into the techniques of working with null values, let’s first understand what null means in SQL. Null is not a value, but a special marker that indicates that there is no data in a column. For example, if you have a table of customers, and some of them do not have a phone number, you can use null to represent the missing data in the phone column.

Null is different from zero, blank, or empty string. Zero is a numeric value, blank is a string value with no characters, and empty string is a string value with zero length. Null is not a value at all, and therefore cannot be compared or operated with other values using the normal operators like =, <, >, or <>.

How to Test for Null Values in SQL?

Since null is not a value, we cannot use the normal comparison operators to test for null values in SQL queries. Instead, we have to use two special operators: IS NULL and IS NOT NULL. These operators return true or false depending on whether the column value is null or not.

1)IS NULL

The IS NULL operator is used to filter the rows that have null values in a column. The syntax is:

SQL

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

For example, the following query returns the customers who do not have a phone number:

SQL

SELECT CustomerID, Name, Phone
FROM Customers
WHERE Phone IS NULL;

2)IS NOT NULL

The IS NOT NULL operator is used to filter the rows that have non-null values in a column. The syntax is:

SQL

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

For example, the following query returns the customers who have a phone number:

SQL

SELECT CustomerID, Name, Phone
FROM Customers
WHERE Phone IS NOT NULL;

How to Handle Null Values in SQL Functions?

SQL provides some built-in functions that can help us deal with null values in a more flexible and convenient way. These functions are:

COALESCE: Returns the first non-null value in a list of expressions.
NULLIF: Returns null if two expressions are equal, otherwise returns the first expression.
ISNULL: Returns a specified value if the expression is null, otherwise returns the expression. (This function is specific to SQL Server and MySQL)

Let’s see how these functions work and when to use them.

COALESCE:

The COALESCE function takes a list of expressions as arguments and returns the first non-null expression in the list. If all the expressions are null, it returns null. The syntax is:

SQL

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

For example, the following query returns the name of the customer, or ‘Unknown’ if the name is null:

SQL

SELECT CustomerID, COALESCE(Name, 'Unknown') AS Name, Phone
FROM Customers;

The COALESCE function is useful when you want to provide a default value for a column that may contain null values, or when you want to combine multiple columns into one and choose the first non-null value.

NULLIF:

The NULLIF function takes two expressions as arguments and returns null if they are equal, otherwise returns the first expression. The syntax is:

SQL

NULLIF(expression1, expression2)

For example, the following query returns the phone number of the customer, or null if the phone number is zero:

SQL

SELECT CustomerID, Name, NULLIF(Phone, 0) AS Phone
FROM Customers;

The NULLIF function is useful when you want to exclude some values from your analysis or calculation, or when you want to avoid division by zero errors.

ISNULL:

The ISNULL function takes two expressions as arguments and returns the second expression if the first expression is null, otherwise returns the first expression. The syntax is:

SQL

ISNULL(expression1, expression2)

For example, the following query returns the phone number of the customer, or ‘No Phone’ if the phone number is null:

SQL

SELECT CustomerID, Name, ISNULL(Phone, 'No Phone') AS Phone
FROM Customers;

The ISNULL function is similar to the COALESCE function, but it only takes two arguments and is specific to SQL Server and MySQL. In other databases, you can use the COALESCE function or the CASE expression to achieve the same result.

Conclusion:

Search This Blog

AI