Here we will learn NullIf() function in SQLite and how to use SQLite NullIf() function to return a NULL value if defined parameters are equal.
Generally in SQLite NULLIF() function will accept two parameters and it returns a NULL value in case if both the parameters are equal otherwise it will return first parameter.
The NULLIF() function will return NULL value in case if both the parameters are equal to NULL.
The SQLite NULLIF() function will perform a case-sensitive comparison while comparing two parameters.
Following is the syntax of Nullif() function to return NULL or First parameter value based on the defined parameter values.
nullif( param1, param2 )
The above SQLite nullif() syntax contains few properties those are
param1, param2 – Two parameters to check if param1 = param2 or not to return NULL or first expression value.
Now we will see how to use SQLite Nullif() function to return NULL or first expression based on the defined parameter values with examples.
The following are the simple examples of using SQLite Nullif() Function to return NULL or first expression based on the defined expression values.
sqlite> SELECT nullif('tutlane','tutlane');
nullif("tutlane","tutlane")
---------------------------
NULL
sqlite> SELECT nullif('Tutlane','tutlane');
nullif("Tutlane","tutlane")
---------------------------
Tutlane
sqlite> SELECT nullif('Welcome','Tutlane');
nullif("Welcome","Tutlane")
------------------------
Welcome
sqlite> SELECT nullif(NULL,NULL);
nullif(NULL, NULL)
--------------------------
NULL
Now we will see how to use Nullif() function with table columns for that create tables called emp_master, dept_master and insert some records like as shown following
sqlite> select * from dept_master;
dept_id dept_name
---------- ----------
1 Admin
2 Sales
3 Quality Co
4 Marketing
sqlite> select * from emp_master;
emp_id first_name last_name salary dept_id
---------- ---------- ---------- ---------- ----------
2 Shweta Jariwala 19300 2
3 Vinay Jariwala 35100 3
4 Jagruti Viras 9500 2
5 Shweta Rana 12000 3
6 Sonal Menpara 13000 1
7 Yamini Patel 10000 2
8 Khyati Shah 50000 3
9 Shwets Jariwala 19400 2
Now write the query like as shown following to compare department id of both emp_master and dept_master using NULLIF() function.
sqlite> SELECT nullif((SELECT dept_id FROM dept_master),(SELECT dept_id FROM emp_master));
nullif(dept_master, emp_master)
-------------------------------
4
This is how we can use SQLite Nullif() function to compare two parameter values and get result based on our requirements.