A column in a table can contain NULL values because the column is defined as allow NULL, this has been explained thoroughly in tutorial Nullability Constraint. NULL values can become common because there are situations where a column needs to contain NULLs where assigning a default value is not practical. In other situations, the table designer may not be thoughtful enough to make the column NOT NULL. Therefore, dealing with NULL values has become a day-to-day task for any SQL developers.

To understand what NULL value is, consider this scenario - when inserting a new product to the Products table where the Description column is defined as NULL, there is no need to provide a value for the Description column. So a NULL value can be viewed as a missing unknown value. It's not an empty string and it's also not a zero.

1. Comparing with NULLs

When making comparison with NULLs by using comparison or arithmetic operators +, -, <, >, =, <>, the query returns NULL instead of a Boolean TRUE or FALSE. This includes comparing NULL with itself.

The following example shows when searching for a column that allows NULL, using an expression expr = NULL returns no rows, because expr = NULL is never TRUE.

/*
This query returns nothing because NULL value can't 
be compared to any values, including NULL itself.
*/
select * from employees
where ReportsTo = null;

The following two queries compare NULL with itself and it returns NULL.

select null = null;
 
select null <> null;

To look for a NULL value, you must use the IS NULL keyword or NULL-safe operator <=>. The following two queries show how to find the NULL ReportsTo record in Employees table.

/*
This query returns one record for the Vice President of 
Sales because he does not report to anyone.
*/
select * from employees
where ReportsTo is null;
 
/*
This query returns the same result as query above.
It compares with NULL value by using
NULL-safe operator and it returns one record for 
the Vice President of Sales.
*/
select * from employees
where ReportsTo <=> null;

Query result set - 1 row returned:

In MySQL, the default value for truth from a Boolean operation is 1 and the default value for false from a Boolean operation is 0.

-- Returns 0 which means FALSE.
select 1=0;
 
-- Returns 1 which means TRUE.
select 5=5;
 
-- Returns NULL because using NULL in arithmetic operations does not make sense.
select 5 + null;
 
-- Returns 0 which means FALSE in MySQL.
select 0 is null;
 
-- Returns 1 which means TRUE in MySQL. 
select 0 is not null;
 
-- Returns 0 which means FALSE in MySQL.
select 1 is null;
 
-- Returns 1 which means TRUE in MySQL. 
select 1 is not null;

Using NULL-safe comparison

<=> is NULL-safe equal to. MySQL Documentation states that <=> operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL.

/*
<=> operator returns 1 if both operands 
are NULL, and returns 0 rather than NULL 
if one operand is NULL.
*/
SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;

Query result set: 1 record returned

The following query uses NULL-safe comparison to get the top employee (Vice President, Sales).

/*
NULL-safe comparison returns one row
because both operators are found to
contain NULLs.
 
This query returns the employee who
reports to no one.
*/
SELECT EmployeeID, FirstName, LastName 
FROM Employees
WHERE ReportsTo <=> NULL;

Query result set - 1 row returned

Here is the query by using equal (=) operator. The result is NULL.

/*
Normal equal to (=) comparison returns 
nothing (NULL result) because both 
operators are found to contain NULLs.
 
This query returns no result.
*/
SELECT EmployeeID, FirstName, LastName 
FROM Employees
WHERE ReportsTo = NULL;

Query result set - 0 rows returned

2. Selecting data with NULL values

2.1 COALESCE function returns the first non-NULL value in the comma separated list.

-- This query returns 5 which is the first non-NULL value in the list.
SELECT COALESCE(NULL, 5, 3, NULL, 9) as col;

Query result set - 1 row returned:

-- This query returns NULL because all values are NULL in the list. 
SELECT COALESCE(NULL, NULL, NULL) as col;

Query result set - 0 row returned:

2.2 ISNULL function tests whether an expression is NULL.

This query retrieves the employee who does not report to anyone because ReportsTo column contains NULL for such employee.

-- 1 represents TRUE in MySQL
SELECT EmployeeID, FirstName, LastName
FROM employees 
WHERE ISNULL(ReportsTo)=1;
 
-- This query returns the same result as above.
SELECT EmployeeID, FirstName, LastName
FROM employees
WHERE ISNULL(ReportsTo)=TRUE;

Query result set - 1 row returned:

2.3 Use control flow function to work with NULL values.

The following 3 queries use control flow functions to work out to whom the employee reports. If the ReportsTo column is null, the employee does not report to anyone.

-- Query 1: this query uses case statement.
SELECT t1.FirstName, t1.LastName, 
    case when t1.ReportsTo is null then 'Does not report to anyone' 
    else CONCAT(t2.FirstName, ' ', t2.LastName) 
    end as ReportsTo
FROM employees as t1
left join employees as t2 on t1.ReportsTo=t2.EmployeeID;
 
-- Or 
 
-- Query 2: this query uses IF statement to do the switch which is 
-- equivalent to the case statement above.
SELECT t1.FirstName, t1.LastName, 
   IF(t1.ReportsTo IS NULL, 'Does not report to anyone',
   CONCAT(t2.FirstName, ' ', t2.LastName)) as ReportsTo2    
FROM employees as t1
left join employees as t2 on t1.ReportsTo=t2.EmployeeID;
 
/*
The following is a third way to do the same thing as above. 
It uses IFNULL() function.
 
IFNULL(expr1,expr2): If expr1 is not NULL, returns expr1; otherwise it returns expr2.
 
Query 3: this query returns the same result as above, but here it uses 
the IFNULL function to do NULL value checking.
*/
SELECT t1.FirstName, t1.LastName, 
   IFNULL(CONCAT(t2.FirstName, ' ', t2.LastName),'Does not report to anyone') as ReportsTo
FROM employees as t1
left join employees as t2 on t1.ReportsTo=t2.EmployeeID;

Query result set - 9 rows returned:

2.4 NULLIF() function

MySQL NULLIF() returns NULL when the first is equal to the second expression, otherwise, it returns the first expression.

NULLIF(expr1,expr2) returns NULL if expr1 = expr2 is true, otherwise returns expr1. This is equivalent to using case statement CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.

-- This query returns NULL.
SELECT NULLIF(1,1);
 
-- This query returns 1 because 1 does not equal to 2 so 1 is returned.
SELECT NULLIF(1,2);

The following query combines NULLIF with case statement.

SELECT t1.FirstName, t1.LastName, 
    case when NULLIF(t1.ReportsTo,NULL) is null then 'Does not report to anyone' 
    else CONCAT(t2.FirstName, ' ', t2.LastName) 
    end as ReportsTo
FROM employees as t1
left join employees as t2 on t1.ReportsTo=t2.EmployeeID;

Query result set - 9 rows returned:

3. NULL values and ORDER BY clause

When using a column which contains NULL values in the ORDER BY clause, NULL values are presented first if you do ORDER BY in ascending order, whereas NULL values are presented last if you do ORDER BY in descending order.

This query lists Vice President Andrew Fuller as the first record because its ReportsTo contains NULL and the ORDER BY is by ascending order (no need to specify ASC keyword because the default sorting assumes ascending order).

select FirstName, LastName, Title from Employees
order by ReportsTo;

Query result set - 9 rows returned:

This query lists Vice President Andrew Fuller as the last record because DESC is specified in the ORDER BY clause.

select FirstName, LastName, Title from Employees
order by ReportsTo DESC;

Query result set - 9 rows returned:

4. Use NULL values with aggregate (summary) functions

4.1 Differences between COUNT(*), COUNT(expr), and COUNT(distinct expr)

COUNT(*) returns a count of the number of rows retrieved, whether or not they contain NULL values.

select count(*) from employees;

Query result set - 1 row returned:

COUNT(expr) only returns a count of non-NULL values because NULL values are ignored.

Query below returns 8 rather than 9 because the Vice President Andrew Fuller does not report to anyone so his record is not counted.

select count(ReportsTo) from employees;

Query result set - 1 row returned:

When extracts distinct records, NULL values are included. When counting the number of distinct records, NULL values are ignored.

The following query returns 3 records, including the NULL value.

select distinct ReportsTo from employees;

Query result set - 3 rows returned:

But if you count the distinct records, it returns 2 because COUNT function only returns a count of non-NULL values.

select count(distinct ReportsTo) from employees;

Query result set - 1 row returned:

4.2 Aggregate functions MAX(), MIN(), SUM(), etc., ignore NULL values.

4.3 When using GROUP BY with a column containing NULL values, the NULLs are all put together into one group.

select ReportsTo, count(*) from employees
group by ReportsTo;

Query result set - 3 rows returned:

4.4 ROLLUP

ROLLUP generates total value for the column used in GROUP BY clause. MySQL always returns NULL in the rollup column to indicate this row is for the total, so we need to convert NULL value to a proper label such as 'Total'.

Query below displays NULL in the last row for the Title column which is rolled up to calculate the total of occurrences for all titles.

select TitleOfCourtesy as Title, 
       count(*) as occurrences 
from employees
group by TitleOfCourtesy with ROLLUP;

Query result set - 5 rows returned:

This query displays the word Total for the last row by converting NULL to Total.

select if(TitleOfCourtesy <=> null, 'Total', TitleOfCourtesy) as Title, 
       count(*) as occurrences 
from employees
group by TitleOfCourtesy with ROLLUP;

Query result set - 5 rows returned:

5. Using NULL values in join columns

INNER JOIN skips NULL values. The following query is an inner join and it returns 8 records where Vice President Andrew Fuller's record is not included because NULL values can't be joined with anything. The workaround to include NULL values is to use LEFT or RIGHT JOIN.

select t1.FirstName, t1.LastName,
    case when t1.ReportsTo is null then 'Does not report to anyone' 
    else CONCAT(t2.FirstName, ' ', t2.LastName) 
    end as ReportsTo
from employees as t1
join employees as t2 on t1.ReportsTo=t2.EmployeeID;

Query result set - 8 rows returned:

LEFT or RIGHT OUTER JOIN retains NULL values. The following query returns 9 records where Vice President Andrew Fuller's record is kept.

select t1.FirstName, t1.LastName, 
    case when t1.ReportsTo is null then 'Does not report to anyone' 
    else CONCAT(t2.FirstName, ' ', t2.LastName) 
    end as ReportsTo
from employees as t1
left join employees as t2 on t1.ReportsTo=t2.EmployeeID;

Query result set - 9 rows returned:

6. Insert a NULL value or a default value.

When executing an insert query, it's always a good practice to specify a default value for a nullable column. This makes table maintenance much easier.

For character type of columns, the default value can be an empty string '', 'Unknown', or 'NA' (Not Applicable).

For a date or time type of columns, specify a date in the far past or far future. For example, in HR table, for a continuing/permanent position, the employment End Date can be set to 12/31/2999 to indicate a long term contract of employment.

For numeric type of columns, the default value can be 0 or -1, or any other value that suits your situation. If the numeric column is a foreign key column with referential integrity enforced, you need to add the default value 0 or -1 as primary key in the parent table (also known as master or referenced table, or primary key table). Normally 0 represents 'Unknown' and -1 represents 'NA.

The following query will fail with Error Code 1048 because CompanyName column cannot be NULL and no DEFAULT constraint is defined for this column.

-- Fail with Error Code 1048.
INSERT INTO shippers
(ShipperID,CompanyName,Phone)
VALUES(4,null,'(503) 555-9831');

This query inserted a NULL value in ReportsTo column and succeeded because the column allows NULL.

-- Succeed
INSERT INTO employees
(EmployeeID,LastName,FirstName,Title,TitleOfCourtesy,BirthDate,HireDate,
Address,City,Region,PostalCode,Country,HomePhone,Extension,Photo,Notes,ReportsTo)
VALUES(10,'Cook','David','Vice President, Marketing','Mr.','1961-02-26 00:00:00',
'1994-12-02 00:00:00','565 King Street','Seattle','WA',
'892689','USA','(303) 999-5689','5689','david.jpg','Marketing degree from Harvard University.',null);

The new employee inserted is shown below.

7. Use NULLs with TIMESTAMP column or AUTO_INCREMENT column

For some data types, MySQL handles NULL values specially. If you insert NULL into a TIMESTAMP column, the current date and time is inserted. If you insert NULL into an integer column that has the AUTO_INCREMENT attribute, the next number in the sequence is inserted.

In query below, we deliberately omitted EmployeeID column and we didn't specify a value for it. After run, it automatically inserted EmployeeID 11 because it's the next auto number sequence to use.

INSERT INTO employees
(LastName,FirstName,Title,TitleOfCourtesy,BirthDate,HireDate,
Address,City,Region,PostalCode,Country,HomePhone,Extension,Photo,Notes,ReportsTo)
VALUES('Lim','Julie','Sales Consultant','Mrs.','1973-12-12 00:00:00','1998-03-15 00:00:00',
'333 Central Avenue','Seattle','WA',
'24289','USA','(202) 111-2345','5689','julie.jpg',
'Julie has a BA degree in Psychology from the University of Washington.',2);

The new employee inserted is shown below.

8. Indexing a nullable column

You can add an index on a column that have NULL values if you are using the MyISAM, InnoDB, or BDB, or MEMORY storage engine. Otherwise, you must declare the indexed column NOT NULL, and you cannot insert NULL into the column.

Happy Coding!

Other tutorials in this category

1. MySQL Northwind Queries - Part 1

2. MySQL Northwind Queries - Part 2

3. MySQL Northwind Queries - Part 3

4. How to Work with Two Unrelated Values

5. How to Fill Gaps in Sales Data

6. How to Calculate Totals, Subtotals and Grand Total

7. How to fill down empty cells with values from a previous non-empty row

8. Use RANK function to update a previous record within a group or partition

9. Two ways to add a unique number or ID to each row

10. 3 ways to get Top N rows from MySQL

11. How to generate Cumulative Sum (running total) by MySQL - Part 1

12. How to generate Cumulative Sum (running total) by MySQL - Part 2

Back to Tutorial Index Page