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.
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 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
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
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
Follow the reactions below and share your own thoughts.