- SQL Tutorial
- SQL - Home
- SQL - Overview
- SQL - RDBMS Concepts
- SQL - Databases
- SQL - Syntax
- SQL - Data Types
- SQL - Operators
- SQL - Expressions
- SQL Database
- SQL - Create Database
- SQL - Drop Database
- SQL - Select Database
- SQL - Rename Database
- SQL - Show Databases
- SQL - Backup Database
- SQL Table
- SQL - Create Table
- SQL - Show Tables
- SQL - Rename Table
- SQL - Truncate Table
- SQL - Clone Tables
- SQL - Temporary Tables
- SQL - Alter Tables
- SQL - Drop Table
- SQL - Delete Table
- SQL - Constraints
- SQL Queries
- SQL - Insert Query
- SQL - Select Query
- SQL - Select Into
- SQL - Insert Into Select
- SQL - Update Query
- SQL - Delete Query
- SQL - Sorting Results
- SQL Views
- SQL - Create Views
- SQL - Update Views
- SQL - Drop Views
- SQL - Rename Views
- SQL Operators and Clauses
- SQL - Where Clause
- SQL - Top Clause
- SQL - Distinct Clause
- SQL - Order By Clause
- SQL - Group By Clause
- SQL - Having Clause
- SQL - AND & OR
- SQL - BOOLEAN (BIT) Operator
- SQL - LIKE Operator
- SQL - IN Operator
- SQL - ANY, ALL Operators
- SQL - EXISTS Operator
- SQL - CASE
- SQL - NOT Operator
- SQL - NOT EQUAL
- SQL - IS NULL
- SQL - IS NOT NULL
- SQL - NOT NULL
- SQL - BETWEEN Operator
- SQL - UNION Operator
- SQL - UNION vs UNION ALL
- SQL - INTERSECT Operator
- SQL - EXCEPT Operator
- SQL - Aliases
- SQL Joins
- SQL - Using Joins
- SQL - Inner Join
- SQL - Left Join
- SQL - Right Join
- SQL - Cross Join
- SQL - Full Join
- SQL - Self Join
- SQL - Delete Join
- SQL - Update Join
- SQL - Left Join vs Right Join
- SQL - Union vs Join
- SQL Keys
- SQL - Unique Key
- SQL - Primary Key
- SQL - Foreign Key
- SQL - Composite Key
- SQL - Alternate Key
- SQL Indexes
- SQL - Indexes
- SQL - Create Index
- SQL - Drop Index
- SQL - Show Indexes
- SQL - Unique Index
- SQL - Clustered Index
- SQL - Non-Clustered Index
- Advanced SQL
- SQL - Wildcards
- SQL - Comments
- SQL - Injection
- SQL - Hosting
- SQL - Min & Max
- SQL - Null Functions
- SQL - Check Constraint
- SQL - Default Constraint
- SQL - Stored Procedures
- SQL - NULL Values
- SQL - Transactions
- SQL - Sub Queries
- SQL - Handling Duplicates
- SQL - Using Sequences
- SQL - Auto Increment
- SQL - Date & Time
- SQL - Cursors
- SQL - Common Table Expression
- SQL - Group By vs Order By
- SQL - IN vs EXISTS
- SQL - Database Tuning
- SQL Function Reference
- SQL - Date Functions
- SQL - String Functions
- SQL - Aggregate Functions
- SQL - Numeric Functions
- SQL - Text & Image Functions
- SQL - Statistical Functions
- SQL - Logical Functions
- SQL - Cursor Functions
- SQL - JSON Functions
- SQL - Conversion Functions
- SQL - Datatype Functions
- SQL Useful Resources
- SQL - Questions and Answers
- SQL - Quick Guide
- SQL - Useful Functions
- SQL - Useful Resources
- SQL - Discussion
SQL - NULLIF() Function
The SQL NULLIF() function is, used to verify whether the two expressions are equal or not.
It accepts two parameters expr1 and expr2 and returns NULL, if both the expression are equal; else returns the first expression.
Note − It always returns the first expression if both expressions are not equal, no matter whether the first expression is greater, or the second expression.
Syntax
Following is the syntax of the SQL NULLIF() function −
NULLIF(expr1, expr2);
Parameters
expr1 − It is the first expression.
expr2 − It is the second expression.
Return value
This function returns NULL if both expressions are equal.
Example
If the both expressions are equal, the NULLIF() function returns a NULL value.
The SQL NULLIF() function is used in the following program to determine whether the first expression 100 is equal to the second expression 100 or not.
SELECT NULLIF(100, 100) As Result;
Output
Following is the output of the above query −
+--------+ | Result | +--------+ | NULL | +--------+
Example
If both expressions are different, this function returns the first expression.
In this example, we are using the SQL NULLIF() function to verify whether the first expression '50' is equal to the second expression '30' or not.
SELECT NULLIF(50, 30) As Result;
Output
On executing the above statement, it produces the following output −
+--------+ | Result | +--------+ | 50 | +--------+
Example
Following is another example of the SQL NULLIF() function, here, we are using this function to compare two strings, whether they are equal or not.
DECLARE @EXPR1 VARCHAR(10) = 'HELLO', @EXPR2 VARCHAR(10) = 'HELLOWORLD'; SELECT NULLIF(@EXPR1, @EXPR2) AS RESULT;
Output
The above SQL statement produces the following output −
+--------+ | Result | +--------+ | HELLO | +--------+
Example
If we pass the first expression value as NULL to the function, this function throws an error.
In this program, we are passing the first expression value as NULL to the NULLIF() function to compare it with the second expression 20.
SELECT NULLIF(NULL, 20) As Result;
Output
After executing the above query, it produces the following output −
The type of the first argument to NULLIF cannot be the NULL constant because the type of the first argument has to be known.
Example
You can also pass the table column as an argument to the SQL NULLIF() function to check whether the content of the two columns is equal or not. Assume we have created a table with the name Customer using the CREATE statement as follows −
CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2));
Now, let's insert some records in to the Customers table using the INSERT statement as shown below −
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'kaushik', 23, 'Kota', 2000.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (5, 'Ganesh', 32, 'Punjab', 2200.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (6, 'Rohan', 32, 'Delhi', 2250.00 );
The following SQL query checks whether the customer Age(first expression) is equal to the specified second expression or not, if equal, it returns NULL; else returns the first expression(customer age) in the Customers table −
SELECT ID, NAME, AGE, NULLIF(AGE, 32) AS RESULT FROM CUSTOMERS;
Output
The above SQL query generate the following output −
+----+----------+-------+---------+ | ID | NAME | AGE | RESULT | +----+----------+-------+---------+ | 1 | Ramesh | 32 | NULL | | 2 | Khilan | 25 | 25 | | 3 | kaushik | 23 | 23 | | 4 | Chaitali | 25 | 25 | | 5 | Ganesh | 32 | NULL | | 6 | Rohan | 32 | NULL | +----+----------+-------+---------+
To Continue Learning Please Login
Login with Google