SQL NULLIF Function

Summary: in this tutorial, you will learn how to use the SQL NULLIF function to compare two values and return NULL if they are equal.

Introduction to the SQL NULLIF function #

The NULLIF function compares two values and returns NULL if they are equal.

Here’s the syntax of the NULLIF function:

NULLIF(value1,value2);

The NULLIF function returns NULL if value1 equals value2. It returns value1 if the values are not equal.

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

CASE
  WHEN value1 = value1 THEN NULL
  ELSE value1
END

Basic SQL NULLIF function examples #

The following example uses the NULLIF function with two arguments are 100:

SELECT
  NULLIF(100, 100) result;

Try it

Output:

 result
--------
   NULL

The following example returns the first argument which is 100 because 100 is not equal to 200:

SELECT
  NULLIF(100, 200) result;

Try it

 result
--------
    100

The following statement returns the first argument which is 100 because 100 is not equal to NULL.

SELECT
  NULLIF(100, NULL) result;

Try it

Output:

 result
--------
    100

The following statement returns the first argument which is NULL because NULL is not equal to 100:

SELECT
  NULLIF(NULL, 100) result;

Try it

Output:

 result
--------
   NULL

The following statement returns the NULL because the string SQL is equal to the string SQL:

SELECT
  NULLIF('SQL', 'SQL') result;

Try it

Output:

 result
--------
 NULL

Using NULLIF function with table data #

First, create a new table calledarticles to store articles:

CREATE TABLE articles (
  article_id INT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  excerpt VARCHAR(255),
  body TEXT
);

Try it

Second, insert some rows into the articles table:

INSERT INTO
  articles (article_id, title, excerpt, body)
VALUES
  (
    1,
    'SQL NULLIF function',
    '',
    'This tutorial shows you how to use the SQL NULLIF function'
  ),
  (
    2,
    'SQL tutorial',
    'Learn how to use SQL at sqltutorial.org',
    'You will learn SQL with practical examples'
  ),
  (
    3,
    'SQL query',
    NULL,
    'You will learn how to use SELECT statement to query data from tables'
  );

Try it

Third, retrieve article_id, title and excerpt from the articles table:

SELECT
  article_id,
  title,
  excerpt
FROM
  articles;

Try it

Output:

 article_id |        title        |                 excerpt
------------+---------------------+-----------------------------------------
          1 | SQL NULLIF function |
          2 | SQL tutorial        | Learn how to use SQL at sqltutorial.org
          3 | SQL query           | NULL

If the excerpt is not available, we can use the first 50 characters of the body instead.

To do that, we 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;

Try it

Output:

 article_id |        title        |                      summary
------------+---------------------+----------------------------------------------------
          1 | SQL NULLIF function |
          2 | SQL tutorial        | Learn how to use SQL at sqltutorial.org
          3 | SQL query           | You will learn how to use SELECT statement to quer

However, The summary column is empty 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;

Try it

Output:

 article_id |        title        |                      summary
------------+---------------------+----------------------------------------------------
          1 | SQL NULLIF function | This tutorial shows you how to use the SQL NULLIF
          2 | SQL tutorial        | Learn how to use SQL at sqltutorial.org
          3 | SQL query           | You will learn how to use SELECT statement to quer

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

Summary #

Use the NULLIF function to compare two values and returns NULL if they are equal.

Quiz #

Quiz

SQL NULLIF Function

6 questions

To help you understand the usage and functionality of the SQL NULLIF function.

Databases #

Was this helpful?