SQL IFNULL Function

Q: How is IFNULL different from COALESCE?

IFNULL accepts exactly two arguments and returns the first non-NULL one. COALESCE accepts two or more arguments and returns the first non-NULL value from the list, making it more flexible and standard SQL.

SQL > SQL NULL > IFNULL Function

The IFNULL( ) function is available in MySQL and SparkSQL, and not in SQL Server, Oracle, or HiveQL (Hive SQL). This function takes two arguments. If the first argument is not NULL, the function returns the first argument. Otherwise, the second argument is returned. This function is commonly used to replace NULL value with another value. It is similar to the NVL function in Oracle and the ISNULL Function in SQL Server.

IFNULL(expr1, expr2) returns the first expression if it is not NULL, otherwise it returns the second. This makes it ideal for substituting default values in place of NULL in MySQL and SparkSQL queries.

For example, if we have the following table,

Table Sales_Data

Store_Name	Sales
Store A	300
Store B	NULL

The following SQL,

SELECT SUM(IFNULL(Sales,100)) FROM Sales_Data;

Result:

SUM(IFNULL(Sales,100))

400

This is because NULL has been replaced by 100 via the IFNULL function. The total then becomes 300 + 100 = 400.

Frequently Asked Questions

Q: What does the SQL IFNULL function do?
A: IFNULL(expr1, expr2) returns expr1 if it is not NULL; otherwise it returns expr2. It is commonly used to substitute a default value whenever NULL appears in a column.

Q: Which databases support IFNULL?
A: IFNULL is available in MySQL and SparkSQL. It is not supported in SQL Server (use ISNULL instead), Oracle (use NVL instead), or HiveQL.

Q: How is IFNULL different from COALESCE?
A: IFNULL accepts exactly two arguments. COALESCE accepts two or more arguments and returns the first non-NULL value from the list, making it more flexible and part of the SQL standard.

Q: What is the equivalent of IFNULL in Oracle and SQL Server?
A: The Oracle equivalent is NVL(), and the SQL Server equivalent is ISNULL(). All three functions replace a NULL value with a specified default value.

Next: SQL NVL Function

This page was last updated on March 19, 2026.

AdBlock Detected!

Our website is made possible by displaying ads to our visitors. Please supporting us by whitelisting our website.

SQL IFNULL Function

Frequently Asked Questions

Related Topics

AdBlock Detected!