I rather think that
NULLIF(A, B)
is syntactic sugar for
CASE WHEN A = B THEN NULL ELSE A END
But you are correct: it is mere syntactic sugar to aid the human reader.
W3Schools
w3schools.com › sql › func_sqlserver_nullif.asp
SQL Server NULLIF() Function
SQL Examples SQL Editor SQL Quiz SQL Exercises SQL Server SQL Syllabus SQL Study Plan SQL Bootcamp SQL Certificate SQL Training ... The NULLIF() function returns NULL if two expressions are equal, otherwise it returns the first expression.
Microsoft Learn
learn.microsoft.com › en-us › sql › t-sql › language-elements › nullif-transact-sql
NULLIF (Transact-SQL) - SQL Server | Microsoft Learn
November 22, 2024 - Applies to: SQL Server Azure SQL ... in Microsoft Fabric SQL database in Microsoft Fabric · Returns a null value if the two specified expressions are equal....
Videos
07:17
SQL NULLIF function: How to Use the NULLIF Function - YouTube
06:07
SQL SERVER||NULLIF Function in SQL and use case of NULLIF - YouTube
08:49
Using SQL NULLIF with Missing Values | Essential SQL - YouTube
53:00
SQL NULL Functions | COALESCE, ISNULL, NULLIF, IS (NOT) NULL | ...
04:59
Nullif function in sql server - YouTube
21. NULLIF() | SQL NULL FUNCTION | SQL Server Tutorial PART 21 ...
Top answer 1 of 5
9
I rather think that
NULLIF(A, B)
is syntactic sugar for
CASE WHEN A = B THEN NULL ELSE A END
But you are correct: it is mere syntactic sugar to aid the human reader.
2 of 5
8
I often use it where I need to avoid the Division by Zero exception:
SELECT
COALESCE(Expression1 / NULLIF(Expression2, 0), 0) AS Result
FROM …
Hightouch
hightouch.com › sql-dictionary › sql-nullif
SQL NULLIF - Syntax, Use Cases, and Examples | Hightouch
December 29, 2023 - The SQL NULLIF function is used to compare two expressions or values and return NULL if they are equal. It provides a way to handle specific cases where you want to treat equality as a special condition by substituting it with a NULL value.
Snowflake Documentation
docs.snowflake.com › en › sql-reference › functions › nullif
NULLIF | Snowflake Documentation
SELECT a, b, NULLIF(a,b) FROM i; --------+--------+-------------+ a | b | nullif(a,b) | --------+--------+-------------+ 0 | 0 | [NULL] | 0 | 1 | 0 | 0 | [NULL] | 0 | 1 | 0 | 1 | 1 | 1 | [NULL] | 1 | [NULL] | 1 | [NULL] | 0 | [NULL] | [NULL] | 1 | [NULL] | [NULL] | [NULL] | [NULL] | --------+--------+-------------+
GeeksforGeeks
geeksforgeeks.org › sql › nullif-function-in-sql-server
NULLIF() Function in SQL Server - GeeksforGeeks
July 23, 2025 - NULLIF() function in SQL Server is used to check if the two specified expressions are equal or not. If two arguments passed to a function are equal, the NULLIF() function returns Null to us. NULLIF() function operates like a Like a CASE Statement.
Oracle
docs.oracle.com › en › database › oracle › oracle-database › 26 › sqlrf › NULLIF.html
SQL Language Reference
1 month ago - NULLIF compares expr1 and expr2. If they are equal, then the function returns null. If they are not equal, then the function returns expr1.
W3Schools
w3schools.com › sql › sql_isnull.asp
W3Schools.com
String Functions: ASCII CHAR_LENGTH ... CAST COALESCE CONNECTION_ID CONV CONVERT CURRENT_USER DATABASE IF IFNULL ISNULL LAST_INSERT_ID NULLIF SESSION_USER SYSTEM_USER USER VERSION SQL Server Functions...
IBM
ibm.com › docs › en › db2-for-zos › 12.0.0
NULLIF scalar function - Db2 SQL
The NULLIF function returns the null value if the two arguments are equal; otherwise, it returns the value of the first argument.
W3Schools
w3schools.com › sql › func_mysql_nullif.asp
MySQL NULLIF() Function
SQL Examples SQL Editor SQL Quiz SQL Exercises SQL Server SQL Syllabus SQL Study Plan SQL Bootcamp SQL Certificate SQL Training ... The NULLIF() function compares two expressions and returns NULL if they are equal.
Databricks
docs.databricks.com › reference › sql language reference › functions › built-in functions › alphabetical list of built-in functions › nullif function
nullif function | Databricks on AWS
November 9, 2023 - Learn the syntax of the nullif function of the SQL language in Databricks SQL and Databricks Runtime.
ByteScout
bytescout.com › blog › coalesce-nullif-isnull-functions-in-sql.html
COALESCE, NULLIF, and ISNULL function in SQL - ByteScout
June 21, 2023 - NULLIF returns a null value if both arguments are the same. This function in SQL is utilized to study if the two identified values are similar or not. This function yields NULL if the entered two values are identical.
AWS
docs.aws.amazon.com › amazon redshift › database developer guide › sql reference › sql functions reference › conditional expressions › nullif function
NULLIF function - Amazon Redshift
The NULLIF expression compares two arguments and returns null if the arguments are equal. If they are not equal, the first argument is returned.
MSSQLTips
mssqltips.com › home › sql coalesce, isnull, nullif in sql server, oracle and postgresql
SQL COALESCE, ISNULL, NULLIF in SQL Server, Oracle and PostgreSQL
March 21, 2022 - There is another expression/function present in all three RDBMS that is used with NULL and that is NULLIF(). This last function has two arguments and returns NULL if both arguments are equal, otherwise it returns the first argument.
SQLServerCentral
sqlservercentral.com › forums › topic › nullif-and-0
NULLIF and 0 – SQLServerCentral Forums
March 25, 2021 - The NULLIF function returns NULL if the 2 arguments are equal, otherwise it just returns the first argument. The first and third expressions are clearly equivalent expressions, so return NULL.
Databricks Documentation
docs.databricks.com › reference › sql language reference › functions › built-in functions › alphabetical list of built-in functions › nullif function
nullif function | Databricks on Google Cloud
November 9, 2023 - Learn the syntax of the nullif function of the SQL language in Databricks SQL and Databricks Runtime.
Medium
medium.com › @etimfonime › can-you-use-nullif-for-multiple-criteria-in-sql-58ab1ec801db
Can You Use NULLIF() Function for Multiple Criteria in SQL? | by Ime Eti-mfon | Medium
July 11, 2024 - Yes, you can use the NULLIF() function for multiple criteria in SQL by nesting the NULLIF() functions or using it in conjunction with other SQL functions and constructs. However, NULLIF() itself only compares two expressions and returns NULL if they are equal.
DB Vis
dbvis.com › thetable › postgresql-nullif-conditional-logic-made-easier
PostgreSQL NULLIF: Conditional Logic Made Easier
September 11, 2024 - PostgreSQL NULLIF is a standard SQL function that returns NULL if two arguments are equal or the first argument otherwise. On the other hand, ISNULL is not supported by PostgreSQL because it is not a standard SQL function.
TechOnTheNet
techonthenet.com › sql_server › functions › nullif.php
SQL Server: NULLIF Function
In SQL Server (Transact-SQL), the NULLIF function compares expression1 and expression2. If expression1 and expression2 are equal, the NULLIF function returns NULL.
Reddit
reddit.com › r/sql › can someone give me a real world example of the nullif function? i know that it’s supposed to return a bull if the two expressions are equal but i can’t think of a real world scenario where i would use this function.
r/SQL on Reddit: Can someone give me a real world example of the NULLIF function? I know that it’s supposed to return a Bull if the two expressions are equal but I can’t think of a real world scenario where I would use this function.
January 10, 2022 -
Thanks!
Meant Null* not Bull. Apologies.
Top answer 1 of 5
11
This is the main "real world" use: select 1/nullif(foo,0) as foo_inverse Wrapping your divisors in nullif keeps your query from erroring out due to divde by zero. If you want some default value (say X) instead of null as a result, then wrap the whole thing in a nvl(). select nvl(1/nullif(foo,0),X) as foo_inverse
2 of 5
8
I use it a lot because the software I work with typically does not set values to NULL when a user removes a value from a record. It will often set the value to an empty string which I then have to treat the same way as a NULL. Most of my NULL checks look like COALESCE(NULLIF(foo, ''),bar).