Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Go Programs
    • Python Programs
    • Java Programs

MySQL NULLIF Operator

by suresh

The MySQL NULLIF operator is a control flow function, which accepts two arguments. If argument 1 is equal to argument 2, then NULL  returned. Otherwise, argument 1 returned. The syntax behind this MySQL NULLIF is as follows:

SELECT NULLIF(exp1, exp2) 

This NULLIF function returns NULL if exp1 = exp2. Otherwise, it returns the expression 1 value.

MySQL NULLIF Operator Example 1

In this example, we show you the simple NULLIF operator examples. Here, the first statement returns NULL because 10 = 10 is True. The second statement returns 10 because 10 != 100. Within the last statement, we used NULL as an argument value. Here, Null != 10 means the first argument value returned.

SELECT NULLIF(10, 10);

SELECT NULLIF(10, 100);

SELECT NULLIF(NULL, 100), NULLIF(10, NULL);
MySQL NULLIF Operator 1

MySQL NULLIF Example 2

We are using this Customers table for the NULLIF operator demonstration. The below screenshot shows you the data inside this MySQL table

MySQL NULLIF Operator 2

In this example, we used the MySQL NULLIF operator to check the Occupation column value against the Management. It means, if the employee occupation is Management, it returns NULL. Otherwise, the Occupation column value returned.

SELECT EmpID, 
`First Name`,
      `Last Name`,
      Qualification,
      Occupation,
      NULLIF(Occupation, 'Management') AS Result,
      Income,
      Sales,
      HireDate
FROM `MySQL Tutorial`.customer;
MySQL NULLIF Operator 3

This time, we are using the MYSQL NULLIF Operator on the Occupation column and Income. If the occupation is clerical, NULL returned. Next, if the Income is 80000, it returns NULL as the income value.

SELECT EmpID, 
`First Name`,
      `Last Name`,
      Occupation,
      NULLIF(Occupation, 'Clerical') AS OResult,
      Income,
      NULLIF(Income, 80000) AS IResult,
      Sales,
      HireDate
FROM `MySQL Tutorial`.customer;
MySQL NULLIF Operator 4

Placed Under: MySQL

  • How to Download MySQL
  • Install MySQL on Windows
  • MySQL Create Database
  • MySQL Delete Database
  • MySQL Create Table
  • MySQL Drop Table
  • MySQL SELECT Statement
  • MySQL ALIAS Column
  • MySQL Distinct
  • MySQL Insert Statement
  • MySQL Delete
  • MySQL Truncate Table
  • MySQL WHERE Clause
  • MySQL Order By
  • MySQL Group By
  • MySQL Having Clause
  • MySQL LIMIT
  • MySQL Arithmetic Operators
  • MySQL COALESCE Function
  • MySQL AND Operator
  • MySQL NOT Operator
  • MySQL OR Operator
  • MySQL XOR Operator
  • MySQL BETWEEN Operator
  • MySQL Not Between Operator
  • MySQL GREATEST Function
  • MYSQL LEAST Function
  • MySQL LIKE Operator
  • MySQL NOT LIKE Operator
  • MySQL IFNULL Operator
  • MySQL NULLIF Operator
  • MySQL INTERVAL Operator
  • MySQL IS Operator
  • MySQL IN Operator
  • MySQL NOT IN Operator
  • MySQL IS NOT NULL
  • MySQL IS NULL
  • MySQL Inner Join
  • MySQL Cross Join
  • MySQL Right Join
  • MySQL Left Join
  • MySQL Aggregate Functions
  • MySQL Date Functions
  • MySQL Date Function
  • MySQL String Functions
  • MySQL Numeric Functions

Copyright © 2021 · All Rights Reserved by Suresh

About Us | Contact Us | Privacy Policy