SQL NULL

SQL > NULL

In SQL, NULL means that data does not exist. NULL does not equal to 0 or an empty string. Both 0 and empty string represent a value, while NULL has no value.

Any mathematical operations performed on NULL will result in NULL. For example,

10 + NULL = NULL

Aggregate functions such as SUM, COUNT, AVG, MAX, and MIN exclude NULL values. This is not likely to cause any issues for SUM, MAX, and MIN. However, this can lead to confusion with AVG and COUNT.

Let's take a look at the following example:

Table Sales_Data

Store_Name	Sales
Store A	300
Store B	200
Store C	100
Store D	NULL

Below are the results for each aggregate function:

SELECT SUM (Sales), AVG (Sales), MAX (Sales), MIN (Sales), COUNT (Sales)
FROM Sales_Date;

Result:

SUM (Sales)	AVG (Sales)	MAX (Sales)	MIN (Sales)	COUNT (Sales)
600	200	300	100	3

Note that the AVG function counts only 3 rows (the NULL row is excluded), so the average is 600 / 3 = 200, not 600 / 4 = 150. The COUNT function also ignores the NULL row, which is why COUNT (Sales) = 3.

SQL > SQL NULL > ISNULL Function

The ISNULL( ) function is available in both SQL Server and MySQL. However, their uses are different:

SQL Server

In SQL Server, the ISNULL( ) function is used to replace NULL value with another value.

For example, if we have the following table,

Table Sales_Data

Store_Name	Sales
Store A	300
Store B	NULL

The following SQL,

SELECT SUM (ISNULL(Sales,100)) FROM Sales_Data;

returns the following result:

SUM (ISNULL(Sales,100))

400

This is because NULL has been replaced by 100 via the ISNULL function, so the total becomes 300 + 100 = 400.

MySQL

In MySQL, the ISNULL( ) function is used to test whether an expression is NULL. If the expression is NULL, this function returns 1. Otherwise, this function returns 0.

For example,

ISNULL(3*3) returns 0ISNULL(3/0) returns 1




SQL > SQL NULL > IFNULL Function

The IFNULL( ) function is available in MySQL, and not in SQL Server or Oracle. This function takes two arguments. If the first argument is not NULL, the function returns the first argument. Otherwise, the second argument is returned. This function is commonly used to replace NULL value with another value. It is similar to the NVL function in Oracle and theISNULL Function in SQL Server.

For example, if we have the following table,

Table Sales_Data

Store_Name	Sales
Store A	300
Store B	NULL

The following SQL,

SELECT SUM (IFNULL(Sales,100)) FROM Sales_Data;

Result:

SUM (IFNULL(Sales,100))

400

This is because NULL has been replaced by 100 via the IFNULL function. The total then becomes 300 + 100 = 400.

SQL > SQL NULL > NVL Function

The NVL( ) function is available in Oracle, and not in MySQL or SQL Server. This function is used to replace NULL value with another value. It is similar to the IFNULL Function in MySQL and the ISNULL Function in SQL Server.

For example, if we have the following table,

Table Sales_Data

Store_Name	Sales
Store A	300
Store B	NULL
Store C	150

The following SQL,

SELECT SUM (NVL(Sales,100)) FROM Sales_Data;

would generate result below:

SUM (NVL(Sales,100))

550

This is because NULL has been replaced by 100 via the NVL function, hence the sum of the 3 rows is 300 + 100 + 150 = 550.

SQL > SQL NULL > Coalesce Function

The COALESCE function in SQL returns the first non-NULL expression among its arguments. The syntax for COALESCE is as follows:

COALESCE ("expression 1", "expressions 2", ...)

It is the same as the following CASE statement:

SELECT CASE ("column_name")
  WHEN "expression 1 is not NULL" THEN "expression 1"
  WHEN "expression 2 is not NULL" THEN "expression 2"
  ...
  [ELSE "NULL"]
  END
FROM "table_name";

For examples, say we have the following table,

Table Contact_Info

Name	Business_Phone	Cell_Phone	Home_Phone
Jeff	531-2531	622-7813	565-9901
Laura	NULL	772-5588	312-4088
Peter	NULL	NULL	594-7477

and we want to find out the best way to contact each person according to the following rules:

1. If a person has a business phone, use the business phone number.

2. If a person does not have a business phone and has a cell phone, use the cell phone number.

3. If a person does not have a business phone, does not have a cell phone, and has a home phone, use the home phone number.

We can use the COALESCE function to achieve our goal:

SELECT Name, COALESCE (Business_Phone, Cell_Phone, Home_Phone) Contact_Phone
FROM Contact_Info;

Result:

Name	Contact_Phone
Jeff	531-2531
Laura	772-5588
Peter	594-7477

SQL > SQL NULL > NULLIF Function

The NULLIF function takes two arguments. If the two arguments are equal, then NULL is returned. Otherwise, the first argument is returned. The syntax for NULLIF is as follows:

NULLIF ("expression 1", "expressions 2")

It is the same as the following CASE statement:

SELECT CASE ("column_name")
  WHEN "expression 1 = expression 2 " THEN "NULL"
  [ELSE "expression 1"]
  END
FROM "table_name";

For example, let's say we have a table that tracks actual sales and sales goal as below:

Table Sales_Data

Store_Name	Actual	Goal
Store A	50	50
Store B	40	50
Store C	25	30

We want to show NULL if actual sales is equal to sales goal, and show actual sales if the two are different. To do this, we issue the following SQL statement:

SELECT Store_Name, NULLIF (Actual, Goal) FROM Sales_Data;

Result:

Store_Name	NULLIF (Actual, Goal)
Store A	NULL
Store B	40
Store C	25