SQL Null set to Zero for adding

Question

I have a SQL query (MS Access) and I need to add two columns, either of which may be null. For instance:

SELECT Column1, Column2, Column3+Column4 AS [Added Values]
FROM Table

where Column3 or Column4 may be null. In this case, I want null to be considered zero (so 4 + null = 4, null + null = 0).

Any suggestions as to how to accomplish this?

Answer 1

Since ISNULL in Access is a boolean function (one parameter), use it like this:

SELECT Column1, Column2, IIF(ISNULL(Column3),0,Column3) + IIF(ISNULL(Column4),0,Column4) AS [Added Values]
FROM Table

Answer 2

According to Allen Browne, the fastest way is to use IIF(Column3 is Null; 0; Column3) because both NZ() and ISNULL() are VBA functions and calling VBA functions slows down the JET queries.

I would also add that if you work with linked SQL Server or Oracle tables, the IIF syntax also the query to be executed on the server, which is not the case if you use VBA functions.