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.
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
SQL Server – Dealing with NULL values when sorting
When working with NULL values, it’s important how you deal with NULL records in your database. This example walks through and explains how to sort and separate NULL and non-NULL values. This example should work with all database types including MySQL, MS SQL, and postgreSQL.
Let’s start with an example.
Consider an employee table.
eid ename salary commission
----------- -------------------- ----------- -----------
100 jon 2000 NULL
101 tim 2200 NULL
102 mark 2500 NULL
103 steve 3500 NULL
104 king 5500 0
104 ward 1500 300
105 adam 5500 800
Now, you want to sort the results from employee table by the commission column. However, this column is nullable so we will need to specify whether NULL values sort last or first.
The result could contain NULLs sorted either first or sorted last.
select * from employee order by commission;
eid ename salary commission
----------- -------------------- ----------- -----------
100 jon 2000 NULL
101 tim 2200 NULL
102 mark 2500 NULL
103 steve 3500 NULL
104 king 5500 0
104 ward 1500 300
105 adam 5500 800
(7 row(s) affected)
select * from employee order by commission desc;
eid ename salary commission
----------- -------------------- ----------- -----------
105 adam 5500 800
104 ward 1500 300
104 king 5500 0
100 jon 2000 NULL
101 tim 2200 NULL
102 mark 2500 NULL
103 steve 3500 NULL
(7 row(s) affected)
The above queries sort the results for nullable column (commission). If you would like to sort non-NULL values in ascending and descending order and place the NULL values either first or last, you can use sub-queries with case expression.
Using a case expression, you can flag NULL values, and non-NULL values. Once accomplished, you can add a flag column in order by clause. Then you can easily sort non-NULLs values in ascending/descending order or the NULL values in first or last position.
Sorting the non-NULLs values in ascending or descending order.
select eid, ename, salary, commission
from
(select *,
case when commission is null then 1
else 0
end as is_null
from employee) x
order by is_null, commission;
eid ename salary commission
----------- -------------------- ----------- -----------
104 king 5500 0
104 ward 1500 300
105 adam 5500 800
100 jon 2000 NULL
101 tim 2200 NULL
102 mark 2500 NULL
103 steve 3500 NULL
(7 row(s) affected)
In the above query, we have sorted non-NULLs in ascending order with NULLs at last.
Sorting NULLs first and non-NULLs in descending order.
select eid, ename, salary, commission
from
(select *,
case when commission is null then 1
else 0
end as is_null
from employee) x
order by is_null desc, commission desc;
eid ename salary commission
----------- -------------------- ----------- -----------
100 jon 2000 NULL
101 tim 2200 NULL
102 mark 2500 NULL
103 steve 3500 NULL
105 adam 5500 800
104 ward 1500 300
104 king 5500 0
(7 row(s) affected)
Now you can see the results NULL values sorted first followed by non-NULLs in descending order.
NULL effects with IN and NOT IN Clause
Introduction
Treatment of NULL values is an important factor when we are writing query. Sometimes it provide vigorous output if we don't treat NULL. Here in this article I am going to explain a particular scenario with NULL effects.
Scenario
-- Query-1
SELECT 'OK' AS RESULT WHERE 10 IN(1, 2, 4, 10,NULL);
-- Query-2
SELECT 'OK' AS RESULT WHERE 10 NOT IN(1, 2, 4, NULL);
Consider the Query-1 WHERE conditions. It provides the proper output. But in Query-2 not provide any output.
Reason
If we have a closer look, the Query-1 is actually.
SELECT 'OK' AS RESULT WHERE 10 = 1
OR 10 = 2
OR 10 = 4
OR 10 = 10
OR 10 = NULL;
In WHERE condition we find OR operators, so it works fine for 10 = 10.
But for Query-2
SELECT 'OK' AS RESULT WHERE 10 <> 1
AND 10 <> 2
AND 10 <> 4
AND 10 <> NULL;
Here we find the AND operator and 10 is comparing with NULL. Here NULL is UNKNOWN.
ANSI_NULLS ON|OFF
When this is on, any query that compares a value with a null returns a 0. When off, any query that compares a value with a null returns a null value.
What MSDN says
"When SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.
When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the ISO standard. A SELECT statement that uses WHERE column_name =NULL returns the rows that have null values in column_name. A SELECT statement that uses WHERE column_name <> NULL returns the rows that have nonnull values in the column. Also, a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL."
By default the ANSI_NULLS is ON. Now we make it OFF and see the result set of Query-1 and Query-2.
SET ANSI_NULLS OFF
GO
-- Query-1
SELECT 'OK' AS RESULT WHERE 10 IN(1, 2, 4, 10,NULL);
GO
-- Query-2
SELECT 'OK' AS RESULT WHERE 10 NOT IN(1, 2, 4, NULL);
GO
SET ANSI_NULLS ON
GO
Here in this case both the query is returning the same output.
Finding NULL Values
To find NULL values we can use
SELECT * FROM table_name WHERE columns_name IS NULL;
But if ANSI_NULLS is OFF we can write this also
SET ANSI_NULLS OFF
SELECT * FROM table_name WHERE columns_name = NULL;
Or by treating NULL values we can write this
SELECT * FROM table_name
WHERE ISNULL(columns_name, '') = '';
Hope you like it.
