Working with NULL Values in SQL Server

We cannot predict values for most future data points while working with database systems. For example, tables used for customer information often have a mobile number column. It is possible that a customer does not hold a mobile number, or for reasons of privacy, he does not want to share his mobile number. In such cases, we can use NULL values to signify uncertain future data points.

What is NULL?

NULL is a keyword that signifies that no value exists.

Considering NULLs When Designing Tables

We need to determine which fields are going to allow NULL values. When designing tables, we need to consider which fields are required or mandatory to be filled in by the user and which fields are not mandatory. Based on this, we can decide whether to allow NULL values.

CREATE TABLE Customer
(
Customer_id INT		    PRIMARY KEY,
FirstName   VARCHAR(50) NOT NULL,
LastName    VARCHAR(50) NOT NULL,
MobileNo    VARCHAR(15) NULL
);

We have designed a table, Customer, in which every field is required (NOT NULL), except the MobileNo field which allows NULL values.

Note: Primary key constraints are always NOT NULL, so there is no need to specify it explicitly.

NOT NULL indicates a value is mandatory (to be supplied). 
NULL indicates the value is not mandatory.

Inserting NULLs

We are using NULL for the mobile number in Case 2 because we do not have a Mobile Number for CustomerId = 2 right now. Make sure you do not put single quotes around NULL as it will be considered as a string where NULL is a special keyword.

Case 1 : We have a valid MobileNo.

Insert Into dbo.Customer (Customer_id, FirstName, LastName, MobileNo) 
Values (1, 'Hen', 'Kaz', 9833844);

Case 2 : We do not have a MobileNo and, hence, we are inserting it as NULL.

Insert Into dbo.Customer (Customer_id, FirstName, LastName, MobileNo) 
values (2, 'Rec', 'John', NULL);

Querying NULLs

Querying NULLs in SQL is different from querying regular data because we cannot query for the string literial ‘NULL’ or search for a value equal to NULL.

Case 1 : Incorrect Query

SELECT * FROM CUSTOMER 
WHERE MobileNo = NULL

Case 2 : Incorrect Query

SELECT * FROM CUSTOMER 
WHERE MobileNo like 'NULL'

Case 3 : Valid Query to Find MobileNos Having a NULL Value

SELECT * FROM CUSTOMER 
WHERE MobileNo IS NULL

Case 4 : Valid Query to Find MobileNos Not Having a NULL Value

SELECT * FROM CUSTOMER 
WHERE MobileNo IS NOT NULL

Updating NULLS

Now, we have received the mobile number for customer id = 2, which earlier was NULL.
Let us update the mobile number for customer id = 2, where the mobile number is now NULL.

--For customer id 2 whose MobileNo is now NULL 
SELECT * FROM CUSTOMER 
WHERE MobileNo IS NULL And Customer_id = 2

--Update MobileNo
UPDATE CUSTOMER 
SET    MobileNo = 91244
WHERE MobileNo IS NULL And Customer_id = 2

--See the changes
SELECT * FROM CUSTOMER 
WHERE Customer_id = 2

--Setting up MobileNo to NULL again to include it in delete NULLs
UPDATE CUSTOMER 
SET    MobileNo = NULL
WHERE  Customer_id = 2

SELECT * FROM CUSTOMER 
WHERE MobileNo IS NULL And Customer_id = 2

Deleting NULLs

When updating NULLs, it is recommended that you update the MobileNo for Customer Id = 2 to NULL again.

Use the following to delete rows having NULL values:

Delete from Customer
where MobileNo IS NULL