>> Using coalesce might be easier:
>>
>> SELECT COALESCE(T2.ID, T1.ID)
>> FROM table T1
>> LEFT JOIN table T2 on T1.CODE_ID = T2.CODE_ID and T2.LANG_CODE = 'FR_fr'
>> WHERE T1.LANG_CODE = 'EN_en'

>Just FYI, yes, that works too but is much slower then the union or the
>select shown by Svein and Sean.

That's a surprise, I would expect the speed to be similar - at least if you changed to

SELECT COALESCE(T2.ID, T1.ID)
FROM table T1
LEFT JOIN table T2 on T1.CODE_ID = T2.CODE_ID and T2.LANG_CODE||'' = 'FR_fr'
WHERE T1.LANG_CODE = 'EN_en'

Though there are two other drawbacks with this solution:
1) Those that are only defined with LANG_CODE 'FR_fr' and not 'EN_en' will not be included in the result set.
2) You will have to use COALESCE for all fields. Fine if you select one or two fields, less readable if you select hundred fields.

Set