What is equivalent of the Nz Function in MS Access in MySQL? Is Nz a SQL standard?

Question

What is MySQL equivalent of the Nz Function in Microsoft Access? Is Nz a SQL standard?

In Access, the Nz function lets you return a value when a variant is null. Source

The syntax for the Nz function is:

Nz ( variant, [ value_if_null ] )

Answer 1

The COALESCE() function does what you describe. It's standard SQL and it should be supported in all SQL databases.

The IFNULL() function is not standard SQL. Only some brands of databases support this function.

Answer 2

COALESCE does just what the OP is asking for, as does IFNULL:

SELECT Nz(MightBeNullVar, 0) FROM ...  (MS Access version)
SELECT COALESCE(MightBeNullVar, 0) FROM ... (MySQL version)
SELECT IFNULL(MightBeNullVar, 0) FROM ... (MySQL version)

The difference is the COALESCE can search through multiple variables and return the first non-null one:

SELECT COALESCE(MightBeNullVar, MightAlsoBeNullVar, CouldBeNullVar, 0) FROM ... (MySQL version)

each of these will return a 0 (zero) if none of the values have a set value (are null).

The IFNULL is (pretty meaninglessly) faster. There's probably other better things to optimize in your query before bothering with IFNULL vs COALESCE issues. If you have multiple things to check, use COALESCE. If you only have a single value to check, use IFNULL.