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.