Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table my_first_table(col1 int, col2 int, value int);
insert into my_first_table values (10,1,1),(20,2,2),(40,4,4);

create table my_second_table(my_value text, v2 int);
insert into my_second_table values ('my_value_1',1),(null,4);

CREATE TABLE



INSERT 0 3



CREATE TABLE



INSERT 0 2


SELECT  col1,
        col2,
        (SELECT _val FROM
         (SELECT 0 AS _order,coalesce(my_value,'FOUND UNSET') _val
          FROM my_second_table 
          WHERE my_table.value = v2
          UNION ALL SELECT 1 AS _order,'NOT FOUND' as _val) 
         ORDER BY _order LIMIT 1
         ) AS potentially_null_column
FROM my_first_table as my_table


col1
col2
potentially_null_column


10
1
my_value_1

20
2
NOT FOUND

40
4
FOUND UNSET



SELECT 3


SELECT  col1,
        col2,
        coalesce( (SELECT my_value FROM my_second_table WHERE my_table.value = v2)::text
                 ,'UNSET') AS potentially_null_column
FROM my_first_table as my_table


col1
col2
potentially_null_column


10
1
my_value_1

20
2
UNSET

40
4
UNSET



SELECT 3