T-SQL ISNULL

Introduction to T-SQL ISNULL

The following article provides an outline for T-SQL ISNULL. The T-SQL is a built-in system function in T-SQL that can be utilized to examine if the value is null and if it will give back the substitute value defined when calling it, which allows us to give back another matter when the expression is null. When the declared expression is null, then it gives back the provided values. Also, if the provided value is not null, it back the declared expression, it can be included in modern functions; it can receive two parameters like expression and value, and it can be utilized with the different versions of transact-SQL.

The systems can have built-in functions to carry out system operations to provide information about the object and setting, so the ISNULL() is the function that can be used to examine if a given value is null. It will give back the alternate value specified when calling the function. Let us see what the NULL value in T-SQL, in which a NULL value is an unusual marker in a column to indicate that a value does not exist, is; it is necessary to acknowledge that a NULL column value is separate from an empty string or zero value in the column, for example ‘,’ < > NULL, 0 < > NULL.

How to Work T-SQL ISNULL?

As the syntax given below is very simple in which the first argument is the expression that can be utilized for examining the values, in most instances, the expression parameter is only a value. Still, maybe a precise value, and the other parameter means the argument is also a value which can give back a function if the expression is null, the alternate_value is the value which can alter the data type with the same as the expression argument has, for example, if the column is character type then alternate_value also be the character type.

T-SQL ISNULL Function

It is an in-built function in T-SQL that allows us to return the NULL values with a provided alternative value in which this function can give back an alternative value if the expression has the NULL values, the ISNULL() function can be utilized anywhere in which the syntax can grant for the utilization of the function. Still, the primary user of this function is under the SELECT list of a query, and we can say that this function can be helpful when we want to transform any null values.

Syntax:

ISNULL (expression, alternative_value)

Where,

• expression: The particular expression that can be utilized to examine if the value is null.
• alternative_value: A specific value must be returned if the word is NULL.
• returns: It can give back a provided value if the declared expression is null and a declared expression if the provided expression is not null.

Examples of T-SQL ISNULL

Different examples are mentioned below:

Example #1 – ISNULL() for joining the tables.

This function has been utilized for joining two tables; it is also helpful to describe a hardcoded value.

Code:

CREATE TABLE [dbo].[Human] (
[id] [int] NOT NULL IDENTITY(1,1),
[name] [varchar](25),
[addressid] [int],
[businessaddressid] [int]);
CREATE TABLE [dbo].[Address] (
[id] [int] NOT NULL IDENTITY(1,1),
[roadnumber] [int],
[roadname] [varchar](30),
[city] [varchar](30));
INSERT INTO [dbo].[Address] ([roadnumber],[roadname],[city]) VALUES (34,'New St','Leeds');
INSERT INTO [dbo].[Address] ([roadnumber],[roadname],[city]) VALUES (1,'Front St','Bristol');
INSERT INTO [dbo].[Address] ([roadnumber],[roadname],[city]) VALUES (3565,'Main road','Manchester');
INSERT INTO [dbo].[Address] ([roadnumber],[roadname],[city]) VALUES (852,'Deve Ave','London');
INSERT INTO [dbo].[Human] ([name],[addressid],[businessaddressid]) VALUES ('Tim',1,2);
INSERT INTO [dbo].[Human] ([name],[addressid],[businessaddressid]) VALUES ('Oliver',NULL,2);
INSERT INTO [dbo].[Human] ([name],[addressid],[businessaddressid]) VALUES ('Jack',3,NULL);
SELECT P.[name],A.[roadnumber],A.[roadname],A.[city]
FROM [dbo].[Human] P INNER JOIN [dbo].[Address] A
ON ISNULL(P.[addressid],P.[businessaddressid]) = A.[id];

Example #2 – ISNULL() in stored procedures.

This function has also helped sterilize the input parameters within stored procedures; we can examine if parameters are null and allocate a default value, so we do not need to put the same call many times.

Code:

CREATE PROCEDURE SurveyInventory
@p1 int
AS
BEGIN
SET NOCOUNT ON;
SET @p1=ISNULL(@p1,0);
SELECT * FROM [Production].[ProductInventory] WHERE Quantity > @p1;
END
GO

Example #3 – ISNULL() in a view.

Generally, a view is a stored SQL SELECT statement in which the SELECT statement can be helpful in a view definition.

Code:

CREATE VIEW [HumanR].[vEmp_ContactInfo]
AS
SELECT
p.[FName]
,p.[LName]
,pp.[PhNumber]
,pnt.[Name] AS [PhNumberType]
,ea.[EmailAddress]
,ISNULL(cast(p.[AdditionalContactInfo] as varchar(4000)),'None') [AdditionalContactInfo]
FROM [Human].[Human] p
LEFT OUTER JOIN [Human].[HumanPhone] pp
ON pp.BusinessID = p.[BusinessID]
LEFT OUTER JOIN [Person].[PhNumberType] pnt
ON pp.[PhNumberTypeID] = pnt.[PhNumberTypeID]
LEFT OUTER JOIN [Human].[EmailAddress] ea
ON p.[BusinessID] = ea.[BusinessID];

Example #4 – ISNULL() in a trigger.

ISNULL() function in trigger has helped update the column values if they are not described in the INSERT statement.
SELECT * FROM [dbo].[Human] WHERE id=3;

We can generate a trigger on the table below, utilizing the ISNULL() function and returning the null value.

Code:

CREATE TRIGGER TR_Person_BusinessAdressID ON [dbo].[Human]
INSTEAD OF INSERT
AS
SET NOCOUNT ON
INSERT INTO [dbo].[Human] ([name],[addressid],[businessaddressid])
SELECT I.[name],I.[addressid],ISNULL(I.[businessaddressid],I.[addressid])
FROM inserted I
GO

When the below INSERT statement has been called using ‘businessaddressid,’ ‘addressid’ has been simulated.

Code:

INSERT INTO [dbo].[Human] ([name],[addressid]) VALUES ('Laila',3);
SELECT * FROM [dbo].[Human] WHERE name = 'Laila';M inserted I
GO

Example #5 – ISNULL() in a computed column.

The ISNULL() function has been utilized in a computed column.

Code:

SELECT [Title],[FName],[MidName],[LName],[Suffix]
,ISNULL([Title] + ' ','') + [FName] + ' ' + ISNULL([MidName] + ' ','') + [LName] + ISNULL([Suffix],'') AS FullName
FROM [AdventureWorks2017].[Human].[Human];

This query can be used for the computed columns in the table.

Code:

ALTER TABLE [Human].[Human] ADD FullName AS ISNULL([Title] + ' ','') + [FName] + ' ' + ISNULL([MidName] + ' ','') + [LName] + ISNULL([Suffix],'') PERSISTED;

The above query can be reworked as given below.

Code:

SELECT [Title],[FName],[MidName],[LName],[Suffix],[FullName]
FROM [AdventureWorks2017].[Human].[Human];

Conclusion

In this article, we conclude that the ISNULL is the function in T-SQL that has been utilized to examine whether the given value is null or not. It is an advanced function having two parameters; we have also discussed the working and detail of the ISNULL() function.

Recommended Articles

This is a guide to T-SQL ISNULL. Here we discuss the introduction, working, T-SQL ISNULL function, and examples. You may also have a look at the following articles to learn more –

1. SQL ORDER BY DESC
2. SQL EXECUTE
3. SQL EXCLUDE
4. MySQL InnoDB Cluster