SQL NullIf Function

Interactive SQL

For Insert SQL For Update SQL For Select SQL For Delete SQL Learn Programmer
Level SQL

SQL Fundas

SQL Select SQL Insert SQL Update SQL Delete SQL Distinct SQL Where SQL And & Or SQL Order By SQL Data Types SQL Cross Join SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL Self Join SQL Union SQL Top SQL Like SQL Wildcards SQL In SQL Between SQL Alias SQL Select Into SQL Create Table SQL Drop SQL Alter SQL Constraints SQL Not Null SQL Unique SQL Primary Key SQL Foreign Key SQL Check SQL Default SQL Create Index SQL Identity SQL Views SQL Dates SQL Nulls SQL IsNull() SQL NullIf() SQL Create DB

SQL Functions

SQL Functions SQL Count() SQL Sum() SQL Min() SQL Max() SQL Len() SQL Round() SQL Upper() SQL Lower() SQL Avg() SQL Group By SQL Having SQL PatIndex() SQL Substring() SQL Sqrt() SQL Rand() SQL Concat()

« Previous Chapter

Next Chapter »

SQL Tutorial - NullIf Function

NullIf function is a SQL Server function. It returns a null value when two expressions are equal.

The NullIf Function

The 'NullIf' function consists of two parameters. If the first parameter is equal to the second parameter's value then a null value is returned. If it is not equal, then the first expression's value is returned. It acts in a manner that is opposite to that of the IsNull function. The expressions can be constants, column names, functions, subqueries or any combination of arithmetic, bitwise, and string operators. The most common use of NULLIF is to prevent runtime errors such as division by zero.

NullIf Syntax

NULLIF (expression1 , expression2)

NullIf example

SELECT 70/ NULLIF(Col1 + Col2, 0) AS Value

In the above example, lets say we have an arithmetic function wherein we have a difference function in the denominator. The query below will fail if values being supplied to (Col1 + Col2) equal to zero. So in this case the denominator's value will be changed to NULL if it was 0. This will change the result of the entire calculation to NULL instead of causing a runtime error. Now if we use a Is Null function to enclose this whole function and use the second value of the IsNull function as zero, we will get a zero value here.

« Previous Chapter

Next Chapter »