Case trick; "Empty set" handling

A small hack that allows you to handle the "Empty set" in older versions of MySQL (4+), in which there are no functions.


When writing a small query, I stalled on the following problem: there was no requested information in the database. It looked like this:

  set @var = (
     select
         case
             when
                 t1.field is not null
             then
                 t1.field
             else
                 one
         end as field_name
     from
         table1 t1
         join table2 t2 on t1.tid = t2.id
     where
          t1.field2 = '_UNEXISTING_IN_DB_VALUE_'
     order by t1.tid desc
     limit 1
 ); 

')
In this case block, as I just did not try to get one: functions, mathematical, logical operations, but nothing helped: in response, when testing, it was only "Empty set (0.06 sec)".

I already decided to leave the “crutch” written almost immediately in the form:

  set @ var2 = (select if (@var is null, 1, @ var)); 

My old friend helped me find a solution. He advised the following approach: use column numbers instead of their names, as well as use the usual count. As a result, the correct query looks like this:

  set @var = (
     select
         case
             when
                 count (1)> 0
             then
                 t1.field
             else
                 one
         end as field_name
     from
         table1 t1
         join table2 t2 on t1.tid = t2.id
     where
         t1.field2 = '_UNEXISTING_IN_DB_VALUE_'
     order by t1.tid desc
     limit 1
 ); 

see also:
dev.mysql.com/doc/refman/4.1/en/all-subqueries.html