In Structured Query Language, the special identifier NULL is used to denote the absence of a data value from the database. A field with a NULL value has no value at all. As stated, NULL is not equivalent to any number or value.
A column can be specified as not null during table creation or left without any special instructions for the column to accept NULL values. If there is no constraint on the column, NULL will be substituted for the value if it is not given during insertion.
Example:
CREATE TABLE tbSomeNames(Id INT, FirstName VARCHAR(20), MiddleName VARCHAR(20), LastName VARCHAR(20));
INSERT INTO tbSomeNames VALUES(1001,’Zafar’, NULL, ‘Iqbal’)
INSERT INTO tbSomeNames VALUES(1002,’Tahir’, NULL, NULL)
INSERT INTO tbSomeNames VALUES(1003,’John’, ‘M’, ‘Peter’)
The following built-in functions in SQL Server will deal with NULL values.
ISNULL enables us to substitute the desired value for NULL values. It requires only two arguments.
COALESCE assists us in returning the first values in the arguments that are not null.
Refer to the below examples-
SELECT FirstName, ISNULL(MiddleName,”), LastName As PersonName
FROM tbSomeNames;
SELECT FirstName, MiddleName, LastName,
COALESCE(MiddleName,FirstName,LastName) FROM tbSomeNames;
Hope you find this article helpful.
Big Data and SQL 
One comment