What is the syntax to return a user defined string in a left outer join?

I have hit a mental blind spot: - in spite of RTFM and other material I can't see this.

If I ha开发者_运维知识库ve a LEFT OUTER JOIN, what syntax is used to return a column for the right table if it exists, or a user defined string if it does not exist?

user defined string = "My string"

Table 1
id | text
-----------
1  | bloop
2  | grrrr

Table 2
id | flange
-----------
2  | whiz

Desired result
id | text  | flange
-------------------
1  | bloop | My string
2  | grrrr | whiz

The COALESCE function in Firebird 1.5 and higher can convert NULL to most anything else. This enables you to perform an on-the-fly conversion and use the result in your further processing, without the need for “if (MyExpression is null) then” or similar constructions.

see Converting to and from NULL

e.g.

SELECT id,text,COALESCE(flange,'MY string') as flange from ...

SELECT Table1.id,
       ISNULL(Table2.flange, 'UserDefinedString') as flange
FROM 
     Table1

LEFT JOIN Table2
ON Table1.id = Table2.id

SELECT Table1.id,
       COALESCE(Table2.flange, 'UserDefinedString') as flange
FROM 
     Table1

LEFT JOIN Table2
ON Table1.id = Table2.id