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);Code language: SQL (Structured Query Language) (sql)

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
ENDCode language: SQL (Structured Query Language) (sql)

Basic SQL NULLIF function examples #

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

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

Try it

Output:

 result
--------
   NULLCode language: SQL (Structured Query Language) (sql)

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

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

Try it

 result
--------
    100Code language: SQL (Structured Query Language) (sql)

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

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

Try it

Output:

 result
--------
    100Code language: SQL (Structured Query Language) (sql)

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

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

Try it

Output:

 result
--------
   NULLCode language: SQL (Structured Query Language) (sql)

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

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

Try it

Output:

 result
--------
 NULLCode language: SQL (Structured Query Language) (sql)

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
);Code language: SQL (Structured Query Language) (sql)

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'
  );Code language: SQL (Structured Query Language) (sql)

Try it

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

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

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           | NULLCode language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

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 querCode language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

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 querCode 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.

Summary #

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

Quiz #

Databases #

Was this tutorial helpful ?