Click here to Skip to main content
15,396,019 members
Articles / Programming Languages / SQL
Technical Blog
Posted 22 Apr 2021

Tagged as

Stats

3.6K views
4 bookmarked

How I Learned to Stop Worrying and Love NULL in SQL

Rate me:
Please Sign up or sign in to vote.
4.67/5 (2 votes)
22 Apr 2021CPOL5 min read
Write your query in a way to overcome the limitations of NULL rather than avoid using it.
The appropriate SQL function should be used to avoid getting undesired output for operations such as data concatenation, comparison, ORDER BY, or GROUP BY. You should not try to prevent NULL values - instead, write your query in a way to overcome its limitations.

How I Learned to Stop Worrying and Love NULL in SQL

The NULL value is a data type that represents an unknown value. It is not equivalent to empty string or zero. Suppose you have an employee table containing columns such as EmployeeId, Name, ContactNumber and an alternate contact number. This table has a few mandatory value columns like EmployeeId, Name, and ContactNumber. However, an alternate contact number is not required and therefore has an unknown value. Therefore, a NULL value in this table represents missing or inadequate information. Here are other meanings NULL can have:

  • Value Unknown
  • Value not available
  • Attribute not applicable

In this post, we will consider how NULL is used in creating tables, querying, string operations, and functions.

Allowing NULL in CREATE TABLE

To a table structure, we need to define whether the respective column allows NULL or not. For example, look at the following customer's table. The columns such as CustomerID, FirstName, LastName do not allow NULL values, whereas the Suffix, CompanyName, and SalesPerson columns can store NULL values.

SQL
CREATE  TABLE Customers(
	CustomerID SERIAL  PRIMARY  KEY,
	FirstName varchar(50) NOT  NULL,
	MiddleName varchar(50) NULL,
	LastName varchar(50) NOT  NULL,
	Suffix varchar(10) NULL,
	CompanyName varchar(128) NULL,
	SalesPerson varchar(256) NULL,
	EmailAddress varchar(50) NULL
)

Let’s insert a few records into this table using the following script:

SQL
INSERT INTO Customers 
	(FirstName, MiddleName, LastName, Suffix, CompanyName, SalesPerson, EmailAddress)
VALUES
	('John',NULL,'Peter',NULL,NULL,NULL,NULL),
	('Raj','M','Mohan','Mr','ABC','KRS','raj.mohan@abc.com'),
	('Krishna',NULL,'Kumar','MS','XYZ',NULL,'Krishna.kumar@xyz.com')

Using NULL in the WHERE Clause

Now, suppose you want to fetch records for those customers who do not have an email address. The following query works fine, but it will not give us a row:

SQL
Select * FROM Customers WHERE Emailaddress=NULL

How I Learned to Stop Worrying and Love NULL in SQL

Values that are NULL cannot be queried using =

In the above select statement expression defines “Where the email address equals an UNKNOWN value”. In the SQL standard, we cannot compare a value to NULL. Instead, you refer to the value as IS NULL for this purpose. Note: There is a space between IS and NULL. If you remove space, it becomes a function ISNULL().

How I Learned to Stop Worrying and Love NULL in SQL

By using IS NULL instead of equals you can query for NULL values.

Integer, Decimal, and String Operations with NULL

Similarly, suppose you declared a variable but did not initialize its value. If you try to perform an arithmetic operation, it also returns NULL because SQL cannot determine the correct value for the variable, and it considers an UNKNOWN value.

SQL
SELECT 10 * NULL

How I Learned to Stop Worrying and Love NULL in SQL

Multiplying an integer by NULL returns NULL
SQL
SELECT 10.0 * NULL

How I Learned to Stop Worrying and Love NULL in SQL

Multiplying a decimal by NULL returns NULL

NULL also plays an important role in string concatenation. Suppose you required the customer's full name in a single column, and you concatenate them using the pipe sign(||) .

SQL
SELECT Suffix,  FirstName, MiddleName, LastName, Suffix, 
(Suffix || ' ' || FirstName || ' ' || MiddleName || LastName ) _
AS CustomerFullName  FROM Customers

How I Learned to Stop Worrying and Love NULL in SQL

Setting a string to NULL and then concatenating it returns NULL

Look at the result set - the query returns NULL in the concatenated string if any part of the string has NULL. For example,  the person in Row 1 does not have a middle name. Its concatenated string is NULL as well, because SQL cannot validate the string value contains NULL.

There are many SQL functions available to overcome these NULL value issues in string concatenations. We’ll look at them later in this article.

The NULL Value in SQL Aggregates

Suppose you use aggregate functions such as SUM, AVG, or MIN, MAX for NULL values. What do you think the expected outcome would be?

SQL
SELECT Sum(values) AS sum
    ,avg(values) as Avg
    ,Min(Values) as MinValue
    ,Max(Values) as MaxValue
  FROM (VALUES (1), (2), (3),(4), (NULL)) AS a (values);

How I Learned to Stop Worrying and Love NULL in SQL

In aggregate functions, NULL is ignored.

Look at the above figure: it calculated values for all aggregated functions. SQL ignores the NULLs in aggregate functions except for COUNT() and GROUP BY(). You get an error message if we try to use the aggregate function on all NULL values.

SQL
SELECT 
    Sum(values) AS sum
    ,avg(values) as Avg
    ,Min(Values) as MinValue
    ,Max(Values) as MaxValue
           FROM (VALUES (NULL), (NULL), (NULL),(NULL), (NULL)) AS a (values);

How I Learned to Stop Worrying and Love NULL in SQL

Aggregating over all NULL values results in an error.

ORDER BY and GROUP BY with NULL

SQL considers the NULL values as the UNKNOWN values. Therefore, if we use ORDER By and GROUP by clause with NULL value columns, it treats them equally and sorts, group them. For example, in our customer table, we have NULLs in the MilddleName column. If we sort data using this column, it lists the NULL values at the end, as shown below:

SQL
SELECT Suffix,  FirstName, MiddleName, LastName, Suffix, 
(Suffix || ' ' || FirstName || ' ' || MiddleName || LastName )
 AS CustomerFullName
 FROM Customers
 Order BY MiddleName

How I Learned to Stop Worrying and Love NULL in SQL

NULL values appear last in ORDER BY

Before we use GROUP BY, let's insert one more record in the table. It has NULL values in most of the columns, as shown below:

SQL
INSERT INTO Customers (FirstName,MiddleName,LastName,Suffix,CompanyName,
SalesPerson,EmailAddress)
 values('Sant',NULL,'Joseph',NULL,NULL,NULL,NULL);

Now, use the GROUP BY clause to group records based on their suffix.

SQL
SELECT count(*) as Customercount , suffix
    FROM Customers
    Group BY Suffix

How I Learned to Stop Worrying and Love NULL in SQL

GROUP BY does treat all NULL values equally.

As shown above, SQL treats these NULL values equally and groups them. You get two customer counts for records that do not have any suffix specified in the customers table.

Useful Functions for Working with NULL

We explored how SQL treats NULL values in different operations. In this section, we will explore a few valuable functions to avoid getting undesirable values due to NULL.

Using NULLIF in Postgres and MySQL

The NULLIF() function compares two input values.

  • If both values are equal, it returns NULL.
  • In case of mismatch, it returns the first value as an output.

For example, look at the output of the following NULLIF() functions.

 

SQL
SELECT   NULLIF (1, 1); 

How I Learned to Stop Worrying and Love NULL in SQL

NULLIF returns NULL if two values are equal
SQL
SELECT   NULLIF (100,0); 

How I Learned to Stop Worrying and Love NULL in SQL

NULLIF returns the first value if the values are not equal.
SQL
SELECT   NULLIF ('A', 'Z'); 

How I Learned to Stop Worrying and Love NULL in SQL

NULLIF returns the first string in a string compare.

COALESCE function

The COALESCE() function accepts multiple input values and returns the first non-NULL value. We can specify the various data types in a single COALESCE() function and return the high precedence data type.

SQL
SELECT COALESCE (NULL, 2, 5) AS NULLRESPONSE;

How I Learned to Stop Worrying and Love NULL in SQL

COALESCE returns the first non NULL data type in a list.
SQL
SELECT coalesce(null, null, 8, 2, 3, null, 4);
How I Learned to Stop Worrying and Love NULL in SQL

Summary

The NULL value type is required in a relational database to represent an unknown or missing value. You need to use the appropriate SQL function to avoid getting undesired output for operations such as data concatenation, comparison, ORDER BY or GROUP BY. You should not try to prevent NULL values - instead, write your query in a way to overcome its limitations. This way, you will learn to love NULL.

This article was originally posted at https://arctype.com/blog/null-in-sql

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Arctype SQL
Technical Writer Arctype
United States United States
Download our free SQL editor at https://www.arctype.com
Group type: Organisation

3 members


Comments and Discussions

 
Question"Aggregating over all NULL values results in an error." Pin
dbullen25-Apr-21 23:36
Memberdbullen25-Apr-21 23:36 
SuggestionSuggestion Pin
ElectronProgrammer23-Apr-21 2:01
MemberElectronProgrammer23-Apr-21 2:01 
GeneralRe: Suggestion Pin
Arctype SQL23-Apr-21 7:50
professionalArctype SQL23-Apr-21 7:50 
Questionwhy would anyone worry about NULL values in SQL ? Pin
Delphi.7.Solutions23-Apr-21 1:15
MemberDelphi.7.Solutions23-Apr-21 1:15 
AnswerRe: why would anyone worry about NULL values in SQL ? Pin
Arctype SQL23-Apr-21 7:50
professionalArctype SQL23-Apr-21 7:50 
AnswerRe: why would anyone worry about NULL values in SQL ? Pin
Arctype SQL17-May-21 5:03
professionalArctype SQL17-May-21 5:03 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.