SQL Null Values – Tutorial With Examples

SQL NULL values are used to represent missing or unknown data. NULL values are not the same as empty or zero values, and they are also different from values in a database that have not been assigned a value yet. Understanding how to work with NULL values is an important part of using SQL effectively.

What are SQL Null Values?

SQL NULL values are used to indicate that a data value does not exist in the database. This can happen for a variety of reasons, such as when a column in a database table is optional and has not been assigned a value, or when a value has been deliberately removed from a column for some reason.

It’s important to understand that NULL values are not the same as empty or zero values. For example, an empty string is a string that contains no characters, while a NULL value is a value that simply does not exist. Similarly, a zero value is a numerical value of zero, while a NULL value is not a numerical value at all.

Working with SQL Null Values

When working with SQL NULL values, there are a few key concepts and operators that you should be familiar with:

• IS NULL operator: This operator is used to test for NULL values in a column. For example, the following SQL statement would return all rows from the customers table where the city column is NULL:

  SELECT *
  FROM customers
  WHERE city IS NULL;
  

• IS NOT NULL operator: This operator is used to test for non-NULL values in a column. For example, the following SQL statement would return all rows from the customers table where the city column is not NULL:

  SELECT *
  FROM customers
  WHERE city IS NOT NULL;
  

• COALESCE function: This function is used to return the first non-NULL value in a list of expressions. For example, the following SQL statement would return the first non-NULL value in the city, state, and country columns, respectively:

  SELECT COALESCE(city, state, country)
  FROM customers;
  

• NULLIF function: This function is used to return NULL if two expressions are equal. For example, the following SQL statement would return NULL if the value in the city column is equal to the value in the state column:

  SELECT NULLIF(city, state)
  FROM customers;
  

Examples of SQL Null Values

Here are some examples to help illustrate how to work with SQL NULL values:

Example 1: Testing for NULL Values

Consider the following table of customers:

id	name	city	state	country
1	John Doe	New York	NY	USA
2	Jane Doe	NULL	CA	USA
3	Bob Smith	Los Angeles	NULL	USA
4	Sally Johnson	NULL	NULL	Canada

In this example, we can use the IS NULL operator to find all customers with a NULL value in the city column. The following SQL statement would return the following results:

SELECT *
FROM customers
WHERE city IS NULL;

id	name	city	state	country
2	Jane Doe	NULL	CA	USA
4	Sally Johnson	NULL	NULL	Canada

Example 2: Using the COALESCE Function

In this example, we can use the COALESCE function to return the first non-NULL value in the city, state, and country columns, respectively. The following SQL statement would return the following results:

SELECT COALESCE(city, state, country)
FROM customers;

COALESCE(city, state, country)
New York
CA
Los Angeles
Canada

Example 3: Using the NULLIF Function

In this example, we can use the NULLIF function to return NULL if the value in the city column is equal to the value in the state column. The following SQL statement would return the following results:

SELECT NULLIF(city, state)
FROM customers;

NULLIF(city, state)
New York
NULL
Los Angeles
NULL

In conclusion, the IS NULL, COALESCE, and NULLIF functions can be useful in dealing with NULL values in a database. The IS NULL operator is used to find NULL values in a column, COALESCE is used to return the first non-NULL value in a set of columns, and NULLIF is used to return NULL if two values are equal.