Setting NULL values to a custom value in Access-SQL

When LEFT JOINing two tables, is there a way to set the cells which can not be matched (NULL) to a custom value? So e.g. when the result returns, the NULL-cells actually HAVE a value, e.g. "N/A" or "Not found"?

I want to do this in MS Access 2003

Example:

| id | value |               | id | other value |
|----|-------|   LEFT JOIN   |----|-------------|
| 1  | hello |   -- id -->   | 2  | world       |
| 2  | you   |

results in:

| id | value | other value |
| 1  | hello | NULL        |
| 2  | you   | world       |

but should be:

| id | value | other value |
| 1 开发者_如何学Go | hello | custom-val  |
| 2  | you   | world       |

You can use Nz() to substitute an arbitrary value for a NULL;

SELECT Nz(F, "Not Present") FROM T

Would return either the value of field F, or "Not Present" if F were NULL.

Bear in mind that SQL’s outer join is a kind of relational union which is explicitly designed to project null values. You want to avoid using the null value (a good thing too, in my opinion), therefore you should avoid using outer joins. Note that modern relational languages have dispensed with the concept of null and outer join entirely (see endnote).

This outer join:

SELECT DISTINCT T1.id, T1.value, T2.other_value
  FROM T1
       LEFT OUTER JOIN T2
          ON T1.id = T2.id;

…is semantically equivalent to this SQL code:

SELECT T1.id, T1.value, T2.other_value
  FROM T1
       INNER JOIN T2
          ON T1.id = T2.id
UNION
SELECT T1.id, T1.value, NULL
  FROM T1
 WHERE NOT EXISTS (
                   SELECT * 
                     FROM T2
                    WHERE T1.id = T2.id
                  );

The second query may look long winded but that’s only because of the way SQL has been designed/evolved. The above is merely a natural join, a union and a semijoin. However, SQL has no semijoin operator, requires you to specify column lists in the SELECT clause and to write JOIN clauses if your product hasn’t implemented Standard SQL’s NATURAL JOIN syntax (Access hasn’t), which results in a lot of code to express something quite simple.

Therefore, you could write code such as the second query above but using an actual default value rather than the null value.

The only relational game in town is the specification of a D language know as "The Third Manifesto" by Chris Date and Hugh Darwen. It explicitly rejects Codd's nulls (latterly Codd proposed two kinds of null) doesn't accommodate an outer join operator (in more recent writings the authors have proposed relation-valued attributes as an alternative to outer join). Specific citations:

C. J. Date (2009): SQL and Relational Theory: How to Write Accurate SQL Code: Ch 4, 'A remark on outer join' (p.84)

Darwen, Hugh (2003): The Importance of Column Names: "Note that in Tutorial D, the only 'join' operator is called JOIN, and it means 'natural join'." (p.16)

C. J. Date and Hugh Darwen (2006): Databases, Types and the Relational Model: The Third Manifesto: Proscription 4: "D shall include no concept of a 'relation' in which some 'tuple' includes some 'attribute' that does not have a value."