22- SQL Handling NULL values

In SQL, NULL means no value. Null is not equal to 0 or empty string. NULL value can be defined during creating table as below:

 

CREATE TABLE table_name

(col1 data_type NOT NULL,

Col2 data_type,

….

..

COlN data_type NOT NULL );

Here when NOT NULL is not specified, it indicates that this column can have NULL values, it is not necessary to enter values always in the column. A field is NULL value when the field is left blank while creation of table.

Points to note:

  1. You cannot use arithmetic comparison operators such as >,<,<>,= to test for NULL. Any mathematical operation performed on NULL would return NULL value. 
  2. Two NULL values are regarded as equal in GROUP BY.
  3. While using NULLS in ORDER BY, NULL values are displayed first when given ascending order and last if the order is given as descending order.

Example:

CREATE TABLE CUSTOMERS(

CUST_ID INT(2) NOT NULL,

CUST_NAME VARCHAR(20) NOT NULL,

CUST_ADDDR VARCHAR(20));

Here the CUST_ADDR field can have blanks. It would be treated as  NULL value.

The table can be as below:

CUST_ID

CUST_NAME

CUST_ADDR

1

John

24 cross, OH

2

Smith

 

3

Jayne

12,15, CA

4

Max

 

NULL values can be checked using ISNULL or IS NOT NULL operators. Functions like AVG, COUNT, SUM, MIN and MAX exclude NULL values.  IS NULL and IS NOT NULL operators can be used to test for NULL values in SELECT, UPDATE, INSERT and DELETE statements.

IS NULL example:

Get the details of the customers with address as NULL values.

Mysql>SELECT CUST_ID,CUST_NAME,CUST_ADDR

                FROM CUSTOMER

                WHERE CUST_ADDR IS NULL;

CUST_ID

CUST_NAME

CUST_ADDR

2

Smith

 

4

Max

 

 

 IS NOT NULL example:

Get the details of the customers with values in address field.

Mysql>SELECT CUST_ID,CUST_NAME,CUST_ADDR

                FROM CUSTOMER

                WHERE CUST_ADDR IS NOT NULL;

CUST_ID

CUST_NAME

CUST_ADDR

1

John

24 cross, OH

3

Jayne

12,15, CA

 

Example 3:

Use of IS NULL in UPDATE statement.

Suppose you want to update the vendor name for all the records with vendor name as NULL with “COCO” and vendor id as 2. 

UPDATE VENDOR_DETAILS

SET VENDOR_NAME = “COCO”

WHERE VENDOR_NAME IS NULL AND VENDOR_ID = 2;

Example 4:

Consider below data and get results of AVG, COUNT, SUM, MIN and MAX.

Sales_details

Product

Sale

P112

200

P113

100

P114

NULL

P115

500

What would be output of below query?

 

SELECT AVG(SALE),COUNT(SALE),MIN(SALE),MAX(SALE), SUM(SALES)

FROM SALES_DETAILS;

Result would be as below:

 

AVG(SALE)

COUNT(SALE)

MIN(SALE)

MAX(SALE)

SUM(SALE)

266.67

3

100

500

800

In the AVG function, it ignores the NULL value hence the total count is 3 and not 4.

There are different functions for handling NULL values.

1. NULLIF(expr1, expr2)

Here is expr1 and expr2 are equal then NULL will be returned else expr1 will be returned.

Example:

Mysql>SELECT NULLIF(2,2);
+-----------------+
| NULLIF(2,2)     |
+-----------------+
|        NULL     |
+-----------------+
1 row in set (0.08 sec)

Example:

mysql> SELECT NULLIF(2,3);
+-------------+
| NULLIF(2,3) |
+-------------+
|           2 |
+-------------+
1 row in set (0.01 sec)

2. IFNULL(expr1, expr2)

Here the function checks for NULLS in the expressions. If expr1 is not NULL then expr1 is returned else it returns expr2.

Example:

mysql> SELECT IFNULL(1,1);
+-----------------+
| IFNULL(1,1)     |
+-----------------+
|           1     |
+-----------------+
1 row in set (0.05 sec)

mysql> SELECT IFNULL(NULL,20);
+---------------------------+
| IFNULL(NULL,20)           |
+---------------------------+
|              20           |
+---------------------------+
1 row in set (0.01 sec)

mysql> SELECT IFNULL(10,NULL);
+---------------------------+
| IFNULL(10,NULL)           |
+---------------------------+
|              10           |
+---------------------------+
1 row in set (0.00 sec)

3. COALESCE function:

This function is used to return the first non-NULL expression from a list of expressions.

The syntax for COALESCE is :

     COALESCE(expr1, expr2, expr3,…..exprN);

Example:

mysql> SELECT COALESCE(3,7,NULL,10);
+------------------------------------+
| COALESCE(3,7,NULL,10)              |
+------------------------------------+
|                     3              |
+------------------------------------+
1 row in set (0.04 sec)

mysql> SELECT COALESCE(NULL,5,8,10);
+------------------------------------+
| COALESCE(NULL,5,8,10)              |
+------------------------------------+
|                     5              |
+------------------------------------+
1 row in set (0.00 sec)

Let us consider another example as personal details of customer as below:

Cust_details

Cust_id

Cust_name

Addr1

Addr2

Street

1

Jeff

1213

NULL

Lord street

2

Max

NULL

Golden park

Street no 2

3

Jill

NULL

NULL

Street 5

 

Mysql>SELECT CUST_NAME, COALESCE(ADDR1,ADDR2,STREET) AS ADDR FROM CUST_DETAILS

The output would be:

CUST_NAME

ADDR

JEFF

1213

MAX

GOLDEN PARK

JILL

STREET5

 

Like us on Facebook