NULL in SQL Server

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.

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’)

NULL in SQL Server

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.

One comment

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s