Returning a default value in SQL when no rows found

One method uses union all:

select text, name
from text_file
where text = 'met'
union all
select max('Values not found'), NULL
from text_file
where text = 'met'
having count(*) = 0;

Notes. The second subquery is an aggregation query. It always returns one row (without the having). This row has what you are looking for.

Second, this type of operation should really be done in the application and not the database.

Third, if you are only expecting one row, then you can use an aggregation query such as:

select (case when count(*) > 0 then text else 'Values not found' end) as text,
       (case when count(*) > 0 then name end) as name
from text_file
where text = 'met'

Full outer join used like cross join (1=1) gives the requested result.

Version for Oracle:

select nvl(tf.text, er.text) as text, 
       tf.name
from 
(select 'Values not found' text from dual ) er
 full outer join 
text_file tf
on 1 =1

There is no need to group by or execute the query multiple time like in other solutions.

IF Exists (select * from text_file where text='met')
      Begin
           Select text,name From text_file         Where text='met'
      End
Else
      Begin
            Select 'Values not found'
      End

Based on the answer by @dcieslak, here is my MWE in PostgreSQL.

create table text_file (name varchar(100), text varchar(100));

select
  coalesce(tf.name, default_value.name) as name,
  coalesce(tf.text, default_value.text) as text
from text_file tf right outer join
     (select 'Default name' as name, 'Values not found' as text) default_value
     on tf.name = 'met' ;

insert into text_file values ('met', 'met text'), ('foo', 'bar');
-- execute query here
/*
 name |   text
------+----------
 met  | met text
(1 row)
*/

delete from text_file where name = 'met';
-- execute query here
/*
     name     |       text
--------------+------------------
 Default name | Values not found
(1 row)
*/