How to Replace NULL Values in Access SQL: The Equivalent of COALESCE?

Equivalent of the COALESCE function in Access SQL

The COALESCE function is commonly used in SQL Server (T-SQL) to return the first non-NULL value from a parameter list. In Access SQL, the IIF function has similar functionality.

Solution:

To implement the function of COALESCE in Access SQL, you can use the IIF function. The syntax is as follows:

<code>IIf([表达式], TruePart, FalsePart)</code>

In this example, to replace the NULL value in the Price field with 0:

<code>"Price = IIf([Price] Is Null, 0, [Price])"</code>

This expression evaluates whether the Price field is NULL. If true, returns 0; otherwise, returns the Price value itself.

By incorporating this expression into the query, you can handle NULL values ​​efficiently:

<code>SELECT ProductId, "Price = IIf([Price] Is Null, 0, [Price])" AS Price
FROM Products</code>

This query returns the ProductId and modified Price columns, where NULL values ​​have been replaced with 0.

The above is the detailed content of How to Replace NULL Values in Access SQL: The Equivalent of COALESCE?. For more information, please follow other related articles on the PHP Chinese website!