{"id":947,"date":"2016-07-16T14:09:20","date_gmt":"2016-07-16T14:09:20","guid":{"rendered":"https:\/\/sqltutorial.org\/?page_id=947"},"modified":"2025-02-08T06:16:07","modified_gmt":"2025-02-08T13:16:07","slug":"sql-nullif","status":"publish","type":"page","link":"https:\/\/www.sqltutorial.org\/sql-nullif\/","title":{"rendered":"SQL NULLIF Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL NULLIF<\/code> function to compare two values and return NULL<\/code> if they are equal.<\/p>\n\n\n\n

Introduction to the SQL NULLIF function #<\/a><\/h2>\n\n\n\n

The NULLIF<\/code> function compares two values and returns NULL<\/code> if they are equal. <\/p>\n\n\n\n

Here’s the syntax of the NULLIF<\/code> function:<\/p>\n\n\n

NULLIF(value1,value2);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The NULLIF<\/code> function returns NULL<\/code> if value1<\/code> equals value2<\/code>. It returns value1<\/code> if the values are not equal.<\/p>\n\n\n\n
CASE\n  WHEN value1 = value1 THEN NULL\n  ELSE value1\nEND<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Basic SQL NULLIF function examples #<\/a><\/h2>\n\n\n\n
The following example uses the NULLIF<\/code> function with two arguments are 100:<\/p>\n\n\n
SELECT<\/span>\n  NULLIF<\/span>(100<\/span>, 100<\/span>) result<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 result\n--------<\/span>\n   NULL<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following example returns the first argument which is 100<\/code> because 100<\/code> is not equal to 200<\/code>:<\/p>\n\n\n
SELECT<\/span>\n  NULLIF<\/span>(100<\/span>, 200<\/span>) result<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
 result\n--------<\/span>\n    100<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following statement returns the first argument which is 100<\/code> because 100<\/code> is not equal to NULL<\/code>.<\/p>\n\n\n
SELECT<\/span>\n  NULLIF<\/span>(100<\/span>, NULL<\/span>) result<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 result\n--------<\/span>\n    100<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following statement returns the first argument which is NULL<\/code> because NULL<\/code> is not equal to 100<\/code>:<\/p>\n\n\n
SELECT<\/span>\n  NULLIF<\/span>(NULL<\/span>, 100<\/span>) result<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 result\n--------<\/span>\n   NULL<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following statement returns the NULL<\/code> because the string SQL <\/code>is equal to the string SQL<\/code>:<\/p>\n\n\n
SELECT<\/span>\n  NULLIF<\/span>('SQL'<\/span>, 'SQL'<\/span>) result<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 result\n--------<\/span>\n NULL<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Using NULLIF function with table data #<\/a><\/h2>\n\n\n\n
First, create a new table<\/a> calledarticles<\/code> to store articles:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> articles (\n  article_id INT<\/span> PRIMARY KEY<\/span>,\n  title VARCHAR<\/span>(255<\/span>) NOT<\/span> NULL<\/span>,\n  excerpt VARCHAR<\/span>(255<\/span>),\n  body<\/span> TEXT<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, insert some rows<\/a> into the articles<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span>\n  articles (article_id, title, excerpt, body<\/span>)\nVALUES<\/span>\n  (\n    1<\/span>,\n    'SQL NULLIF function'<\/span>,\n    ''<\/span>,\n    'This tutorial shows you how to use the SQL NULLIF function'<\/span>\n  ),\n  (\n    2<\/span>,\n    'SQL tutorial'<\/span>,\n    'Learn how to use SQL at sqltutorial.org'<\/span>,\n    'You will learn SQL with practical examples'<\/span>\n  ),\n  (\n    3<\/span>,\n    'SQL query'<\/span>,\n    NULL<\/span>,\n    'You will learn how to use SELECT statement to query data from tables'<\/span>\n  );<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third,  retrieve article_id<\/code>, title<\/code> and excerpt<\/code> from the articles<\/code> table:<\/p>\n\n\n
SELECT<\/span>\n  article_id,\n  title,\n  excerpt\nFROM<\/span>\n  articles;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 article_id |        title        |                 excerpt\n------------+---------------------+-----------------------------------------<\/span>\n          1 | SQL NULLIF function |\n          2 | SQL tutorial        | Learn how to use<\/span> SQL<\/span> at<\/span> sqltutorial.org\n          3<\/span> | SQL<\/span> query<\/span>           | NULL<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If the excerpt<\/code> is not available, we can use the first 50 characters of the body<\/code> instead. <\/p>\n\n\n\n
SELECT<\/span>\n  article_id,\n  title,\n  COALESCE<\/span>(excerpt, LEFT<\/span>(body<\/span>, 50<\/span>)) AS<\/span> summary\nFROM<\/span>\n  articles;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 article_id |        title        |                      summary\n------------+---------------------+----------------------------------------------------<\/span>\n          1 | SQL NULLIF function |\n          2 | SQL tutorial        | Learn how to use<\/span> SQL<\/span> at<\/span> sqltutorial.org\n          3<\/span> | SQL<\/span> query<\/span>           | You will learn how to<\/span> use<\/span> SELECT<\/span> statement<\/span> to<\/span> quer<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
However, The summary<\/code> column is empty for the article id 1. To solve this problem, as you may guess, we use the NULLIF<\/code> function.<\/p>\n\n\n
SELECT<\/span> \n    article_id, \n    title, \n    COALESCE<\/span>(NULLIF<\/span>(excerpt,''<\/span>), LEFT<\/span>(body<\/span>, 50<\/span>)) AS<\/span> summary\nFROM<\/span>\n    articles;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 article_id |        title        |                      summary\n------------+---------------------+----------------------------------------------------<\/span>\n          1 | SQL NULLIF function | This tutorial shows you how to use<\/span> the SQL<\/span> NULLIF<\/span>\n          2<\/span> | SQL<\/span> tutorial        | Learn how to<\/span> use<\/span> SQL<\/span> at<\/span> sqltutorial.org\n          3<\/span> | SQL<\/span> query<\/span>           | You will learn how to<\/span> use<\/span> SELECT<\/span> statement<\/span> to<\/span> quer<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If the excerpt<\/code> is empty, the NULLIF<\/code> function returns NULL<\/code>, otherwise it returns the excerpt. Then the rest is handled by the COALESCE<\/code> function.<\/p>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
To do that, we use the COALESCE<\/code><\/a> function to return excerpt<\/code> column if it is not NULL<\/code>, otherwise, return the first 50 characters of the body<\/code>.<\/p>\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n
Try it<\/a><\/p>\n\n\n\n
The NULLIF<\/code> function is equivalent to the following searched CASE<\/code><\/a> expression:<\/p>\n\n\n