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:
- You cannot use arithmetic comparison operators such as >,<,<>,= to test for NULL. Any mathematical operation performed on NULL would return NULL value.
- Two NULL values are regarded as equal in GROUP BY.
- 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;
|
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;
|
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:
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:
|