SQL NULLIF Function: Usage of a Handy SQL Control Flow Function

Summary: in this tutorial, you will learn how to use the SQL NULLIF function to add the logic to the queries.

Introduction to the SQL NULLIF function

Besides the COALESCE function, SQL provides you with the NULLIF function which is a control flow function that allows you to test the arguments and returns either the first argument or NULL based on the result of the evaluation.

The NULLIF function accepts two arguments. If the two arguments are equal, the NULLIF function returns NULL, otherwise, it returns the first argument.

The following illustrates the syntax of the NULLIF function.

NULLIF(argument_1,argument_2);Code language: SQL (Structured Query Language) (sql)

The NULLIF function is equivalent to the following searched CASE expression:

CASE WHEN argument_1 = argument_2
   THEN NULL
ELSE
   argument_1 
END;Code language: SQL (Structured Query Language) (sql)

The expression returns NULL if two arguments are equal, otherwise, it returns the argument_1.

SQL NULLIF examples

The following statement returns NULL because 100 is equal to 100.

SELECT NULLIF(100,100);Code language: SQL (Structured Query Language) (sql)

The following statement returns 100 because 100 is less than 200, the NULLIF function returns the first argument which is 100.

SELECT NULLIF(100,200);Code language: SQL (Structured Query Language) (sql)

The following statement returns 100 because 100 is not equal to NULL.

SELECT NULLIF(100,NULL);Code language: SQL (Structured Query Language) (sql)

The following statement returns NULL. Because NULL is not equal to 100, the NULLIF function returns the first argument which is not NULL.

SELECT(NULL,100);Code language: SQL (Structured Query Language) (sql)

The following statement returns SQL literal string because the string SQ is equal to the string SQL.

SELECT NULLIF('SQL','SQL');Code language: SQL (Structured Query Language) (sql)

We will create a new table named articles to demonstrate a real case where we can apply the NULLIF function.

CREATE TABLE articles (
    article_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    excerpt VARCHAR(255),
    body TEXT
);Code language: SQL (Structured Query Language) (sql)

The following INSERT statement inserts some rows into the articles table.

INSERT INTO articles(title,excerpt,body)
VALUES('SQL NULLIF function','', 'This tutorial shows you how to use the SQL NULLIF function'),
	  ('SQL tutorial','Learn how to use SQL at sqltutorial.org', 'You will learn SQL with practical examples'),
      ('SQL query',null, 'You will learn how to use SELECT statement to query data from tables');Code language: SQL (Structured Query Language) (sql)

Suppose we want to display the title and excerpt of articles on an overview page. To do this, we query article_id, title and excerpt as follows:

SELECT 
    article_id, 
    title, 
    excerpt
FROM
    articles;Code language: SQL (Structured Query Language) (sql)

Assuming that if the except is not available, you can  use the first 50 characters of the body to display instead. To do this, you use the COALESCE function to return excerpt column if it is not NULL, otherwise, return the first 50 characters of the body.

SELECT 
    article_id,
    title, 
    COALESCE(excerpt, LEFT(body, 50)) AS summary
FROM
    articles;Code language: SQL (Structured Query Language) (sql)

We have a little issue here.

The summary column is blank for the article id 1. To solve this problem, as you may guess, we use the NULLIF function.

SELECT 
    article_id, 
    title, 
    COALESCE(NULLIF(excerpt,''), LEFT(body, 50)) AS summary
FROM
    articles;Code language: SQL (Structured Query Language) (sql)

If the excerpt is empty, the NULLIF function returns NULL, otherwise it returns the excerpt. Then the rest is handled by the COALESCE function.

Now you should understand how the NULLIF function works and when to apply it your query to make it more flexible.