Saturday, January 9, 2016

SQL - NULL Value

Null is very interesting keyword in the SQL because it is not a data value which indicates that the value is unknown. Another fact is that Null value always differ from an empty or zero value. Simply, we try to compare two null values against each other then the result would be unknown or we try to compare a single null value against itself then the result would be unknown as given below:

---- Declare local veriables to set default null values
Declare @inpIntA int =Null, @inpIntB int =Null
----- Compare both values
IF @inpIntA=@inpIntB
PRINT 'True'
ELSE
PRINT 'False'
----- Compare same value itself
IF @inpIntA=@inpIntA
PRINT 'True'
ELSE
PRINT 'False' 
In the above example, output should be false because in the both cases values are unknown with each other.
In SQL, we can understand that NULL is a value used to indicate that there is no domain specific value in the field. In a very simple words, Null means either "not applicable" or "don't know". NULL is also an SQL reserved keyword used to identify the Null special marker.
We use the null values because the data will be added later. For example, a customer's middle initial name may not be known at the time the customer places an order. In this case, we are going to allow null value for the middle initial name field in the database which could be added in the future.
If a column in a table is optional means we can insert a new record or update an existing record without adding a value to this column. This means that the field will be saved with a NULL value as given below:

----- declare table variable
Declare @Customer Table
(
FirstName Varchar(10) Not NULL,
---- Allow Null Value to the column
MiddleName Varchar(10) NULL,
LastName Varchar(10) Not NULL
)
----- Insert Values here
Insert Into @Customer (FirstName,LastName)
Values
('Ryan','Arjun'),
('Lucy','Gray')
----- Pull Values
SELECT FirstName,MiddleName, LastName
FROM @Customer
FirstName
MiddleName
LastName
Ryan
NULL
Arjun
Lucy
NULL
Gray
----- Update table value
UPDATE @Customer
SET LastName='Singh'
WHERE FirstName='Ryan'
----- Pull Values after update
SELECT FirstName,MiddleName, LastName
FROM @Customer
FirstName
MiddleName
LastName
Ryan
NULL
Singh
Lucy
NULL
Gray
Arithmetic operations with NULL:
  1. If we try to add any value with Null value then output should be null
  2. If we try to subtract any value with Null value then output should be null
  3. If we try to divide any value with Null value then output should be null
  4. If we try to multiply any value with Null value then output should be null
as given below:

---- Declare local variables to set default values
Declare @inpIntA int =Null, @inpIntB int =2
SELECT
----NULL + Any Value
NullPlus=(@inpIntA + @inpIntB),
----NULL - Any Value
NullSubt=(@inpIntA - @inpIntB),
----NULL / Any Value
NullDiv=(@inpIntA / @inpIntB),
----NULL * Any Value
NullMul=(@inpIntA * @inpIntB)
NullPlus
NullSubt
NullDiv
NullMul
NULL
NULL
NULL
NULL

SQL provides the special syntax for testing if a column is null, via is null and is not null, which is a special condition to test for a null (or not a null) as given below:
----- declare table variable
Declare @Customer Table
(
FirstName Varchar(10) Not NULL,
---- Allow Null Value to the column
MiddleName Varchar(10) NULL,
LastName Varchar(10) Not NULL
)
----- Insert Values here
Insert Into @Customer (FirstName,LastName)
Values
('Ryan',NULL,'Arjun'),
('Lucy','Willson','Gray'),
('Uday','Kumar','Chauhan')
----- Pull Values
SELECT FirstName,MiddleName, LastName
FROM @Customer
FirstName
MiddleName
LastName
Ryan
NULL
Arjun
Lucy
Willson
Gray
Uday
Kumar
Chauhan
----- Pull Values Where MiddleName IS NOT NULL
SELECT FirstName,MiddleName, LastName
FROM @Customer WHERE MiddleName IS NOT NULL
FirstName
MiddleName
LastName
Lucy
Willson
Gray
Uday
Kumar
Chauhan

Challenges
Null has been the focus of controversy and a source of debate because of its associated three-valued logic (3VL), special requirements for its use in SQL joins, and the special handling required by aggregate functions and SQL grouping operators. The inconsistencies in the SQL standard mean that it is not possible to ascribe any intuitive logical semantics to the treatment of nulls in SQL.
String concatenation
String concatenation operations, which are common in SQL, also result in Null when one of the operands is Null. The following example demonstrates the Null result returned by using Null as given below:
----- declare table variable
Declare @Customer Table
(
FirstName Varchar(10) Not NULL,
---- Allow Null Value to the column
MiddleName Varchar(10) NULL,
LastName Varchar(10) Not NULL
)
----- Insert Values here
Insert Into @Customer (FirstName,LastName)
Values
('Ryan',NULL,'Arjun'),
('Lucy','Willson','Gray'),
('Uday','Kumar','Chauhan')
----- Pull Values by concating names
SELECT FirstName,MiddleName, LastName,
FullName=FirstName+' '+MiddleName+' '+ LastName
FROM @Customer
FirstName
MiddleName
LastName
FullName
Ryan
NULL
Arjun
NULL
Lucy
Willson
Gray
Lucy Willson Gray
Uday
Kumar
Chauhan
Uday Kumar Chauhan
----- Pull Values with concating names by using ISNULL
SELECT FirstName,MiddleName, LastName,
FullName=ISNULL(FirstName,'')+' '+ISNULL(MiddleName,'')+' '+ ISNULL(LastName,'')
FROM @Customer
FirstName
MiddleName
LastName
FullName
Ryan
NULL
Arjun
Ryan  Arjun
Lucy
Willson
Gray
Lucy Willson Gray
Uday
Kumar
Chauhan
Uday Kumar Chauhan
 
Effect of Null and Unknown in other constructs
Joins evaluate using the same comparison rules as for WHERE clauses. Therefore, care must be taken when using nullable columns in SQL join criteria. We can use the ISNULL keyword to matching conditional data between two table whenever joined them.
CASE expressions: SQL provides two flavours of conditional expressions. One is called "simple CASE" and operates like a switch statement. The other is called a "searched CASE" in the standard, and operates like an if....elseif.
Conclusion
Conceptually, NULL means “a missing unknown value” and it is treated somewhat differently from other values. When null values are present in data, logical and comparison operators can potentially return a third result of UNKNOWN instead of just TRUE or FALSE. This need for three-valued logic is a source of many application errors. These tables outline the effect of introducing null comparisons.

3 comments:

  1. I am a null nazi! No nulls in a data warehouse. You and I may know what it means today, but tomorrow? When we are gone? If there is to be true relationships defined in the data, nulls do not work. If there is a specific meaning then a lookup table must contain a value for that meaning and a code/key should be created to eliminate the null value. ie isnull([field],-1) then in the lookup -1 key must be defined to point to a description/relationship that defines what the null means in the data....

    ReplyDelete
    Replies
    1. 1000% Agree...There should be no such thing as a NULL in a data warehouse, ever....

      Delete
  2. .. another interesting thing is that the equality Null = Null returns false

    ReplyDelete