{"id":1482,"date":"2019-03-03T18:05:35","date_gmt":"2019-03-03T11:05:35","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=1482"},"modified":"2020-07-11T03:50:43","modified_gmt":"2020-07-10T20:50:43","slug":"sql-server-nullif","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-nullif\/","title":{"rendered":"SQL Server NULLIF"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server NULLIF<\/code> expression to return NULL if the first argument equals to the second one.<\/p>\n\n\n\n

SQL Server NULLIF expression overview #<\/a><\/h2>\n\n\n\n

The NULLIF<\/code> expression accepts two arguments and returns NULL<\/code> if two arguments are equal. Otherwise, it returns the first expression.<\/p>\n\n\n\n

The following shows the syntax of the NULLIF<\/code> expression:<\/p>\n\n\n

NULLIF(expression1, expression2)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, the expression1<\/code> and expression2<\/code> are scalar expressions. It means each of them evaluates to a scalar value.<\/p>\n\n\n\n
It is recommended that you not use the time-dependent functions such as RAND()<\/code> function in the NULLIF<\/code> function. Because this may cause the function to be evaluated twice and to yield different results from the two function calls.<\/p>\n\n\n\n
SQL Server NULLIF examples #<\/a><\/h2>\n\n\n\n
Let’s take some examples of using the NULLIF<\/code> expression<\/p>\n\n\n\n
Using NULLIF expression with numeric data examples #<\/a><\/h3>\n\n\n\n
This example returns NULL<\/code> because the first argument equals the second one:<\/p>\n\n\n
SELECT<\/span> \n    NULLIF<\/span>(10<\/span>, 10<\/span>) result<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n
result\n------\nNULL<\/span>\n\n(1<\/span> row affected)<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
However, the following example returns the first argument because two arguments are not equal:<\/p>\n\n\n
SELECT<\/span> \n    NULLIF<\/span>(20<\/span>, 10<\/span>) result<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The output is as follows:<\/p>\n\n\n
result\n------\n20\n\n(1 row affected)<\/code><\/span><\/pre>\n\n\n
Using NULLIF expression with character string data example #<\/a><\/h3>\n\n\n\n
The following example uses the NULLIF<\/code> expression. It returns NULL because the first character string is equal to the second one:<\/p>\n\n\n
SELECT<\/span> \n    NULLIF<\/span>('Hello'<\/span>, 'Hello'<\/span>) result<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n
result\n------<\/span>\nNULL\n\n(1 row affected)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This example returns the first argument because both arguments are not the same:<\/p>\n\n\n
SELECT<\/span> \n    NULLIF<\/span>('Hello'<\/span>, 'Hi'<\/span>) result<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following shows the output:<\/p>\n\n\n
result\n------<\/span>\nHello\n\n(1 row affected)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Using NULLIF expression to translate a blank string to NULL #<\/a><\/h3>\n\n\n\n
The NULLIF<\/code> expression comes in handy when you’re working with legacy data that contains a mixture of null and empty strings in a column. Consider the following example.<\/p>\n\n\n\n
First, create a new table<\/a> named sales.leads<\/code> to store the sales leads:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> sales.leads\n(\n    lead_id    INT<\/span>\tPRIMARY KEY<\/span> IDENTITY<\/span>, \n    first_name VARCHAR<\/span>(100<\/span>) NOT<\/span> NULL<\/span>, \n    last_name  VARCHAR<\/span>(100<\/span>) NOT<\/span> NULL<\/span>, \n    phone      VARCHAR<\/span>(20<\/span>), \n    email      VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
INSERT<\/span> INTO<\/span> sales.leads\n(\n    first_name, \n    last_name, \n    phone, \n    email\n)\nVALUES<\/span>\n(\n    'John'<\/span>, \n    'Doe'<\/span>, \n    '(408)-987-2345'<\/span>, \n    'john.doe@example.com'<\/span>\n),\n(\n    'Jane'<\/span>, \n    'Doe'<\/span>, \n    ''<\/span>, \n    'jane.doe@example.com'<\/span>\n),\n(\n    'David'<\/span>, \n    'Doe'<\/span>, \n    NULL<\/span>, \n    'david.doe@example.com'<\/span>\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SELECT<\/span> \n    lead_id, \n    first_name, \n    last_name, \n    phone, \n    email\nFROM<\/span> \n    sales.leads\nORDER<\/span> BY<\/span>\n    lead_id;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
The phone<\/code> column is a nullable column. If the phone of a lead is not known at the time of recording, the phone column will have NULL.<\/p>\n\n\n\n
However, from the output, the second row has an empty string in the phone<\/code> column due to the data entry mistake. Note that you may encounter a situation like this a lot if you are working with legacy databases.<\/p>\n\n\n\n
To find the leads who do not have the phone number, you use the following query:<\/p>\n\n\n
SELECT<\/span>    \n    lead_id, \n    first_name, \n    last_name, \n    phone, \n    email\nFROM<\/span>    \n    sales.leads\nWHERE<\/span> \n    phone IS<\/span> NULL<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
The output missed one row which has the empty string in the phone<\/code> column. To fix this you can use the NULLIF<\/code> expression:<\/p>\n\n\n
SELECT<\/span>    \n    lead_id, \n    first_name, \n    last_name, \n    phone, \n    email\nFROM<\/span>    \n    sales.leads\nWHERE<\/span> \n    NULLIF<\/span>(phone,''<\/span>) IS<\/span> NULL<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following picture shows the output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
NULLIF and CASE expression #<\/a><\/h2>\n\n\n\n
This expression that uses NULLIF<\/code>:<\/p>\n\n\n
SELECT<\/span> \n    NULLIF<\/span>(a,b)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
CASE \n    WHEN a=b THEN NULL \n    ELSE a \nEND<\/span>\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
See the following example:<\/p>\n\n\n
DECLARE<\/span> @a int<\/span> = 10<\/span>, @b int<\/span> = 20<\/span>;\nSELECT<\/span>\n    NULLIF<\/span>(@a,@b) AS<\/span> result<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n
result\n-----------<\/span>\n10\n\n(1 row affected)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following example returns the same result, but use the CASE<\/code> expression instead:<\/p>\n\n\n
DECLARE<\/span> @a int<\/span> = 10<\/span>, @b int<\/span> = 20<\/span>;\nSELECT<\/span>\n    CASE<\/span>\n        WHEN<\/span> @a = @b THEN<\/span> null<\/span>\n        ELSE<\/span> \n            @a\n    END<\/span> AS<\/span> result<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The CASE<\/code> expression is verbose while the NULLIF<\/code> expression is much shorter and more readable.<\/p>\n\n\n\n
In this tutorial, you have learned how to use the SQL Server NULLIF<\/code> expression to return NULL<\/code> if the first argument equals to the second one.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
is equivalent to the following expression that uses the CASE<\/a><\/code> expression:<\/p>\n\n\n
Third, query<\/a> data from the sales.leads<\/code> table:<\/p>\n\n\n
Second, insert<\/a> three rows into the sales.leads<\/code> table:<\/p>\n\n\n