Return a default value if no rows found

Question

I have the following select statement, to grab the next scheduled item for a stream. If there is no matching row, I want it to return a default value. Here's the line I'm using:

SELECT `file` FROM `show`, `schedule` 
   WHERE `channel` = 1 AND `start_time` <= UNIX_TIMESTAMP() 
   AND `start_time` > UNIX_TIMESTAMP()-1800 AND `show`.`id` = `schedule`.`file` 
   ORDER BY `start_time` DESC LIMIT 1

That should grab the most recently scheduled item, but not if it's older than 30 minutes before the query. However, if the user doesn't schedule anything, I want a default value, so that something actually plays on the stream. I've tried the following:

SELECT COALESCE(`file`, 'default.webm') FROM `show`, `schedule`...

And:

SELECT IFNULL(`file`, 'default.webm') FROM `show`, `schedule`

However, it always returns an empty result if no rows are found. How can I return a default value instead?

Answer 1

For cases where only one row of results is expected, just use UNION with hardcoded value(s) in a second SELECT clause that has the same number of columns as the original SELECT.

For the OP:

(SELECT `file` FROM `show`, `schedule` 
   WHERE `channel` = 1 AND `start_time` <= UNIX_TIMESTAMP() 
   AND `start_time` > UNIX_TIMESTAMP()-1800 AND `show`.`id` = `schedule`.`file` 
   ORDER BY `start_time` DESC LIMIT 1) UNION (SELECT 'default.webm') LIMIT 1;

Barring any syntax errors, the result set will serve up one row with one column called file.

---

As a more general example:

(SELECT Col1,Col2,Col3 FROM ExampleTable WHERE ID='1234')
 UNION (SELECT 'Def Val','none','') LIMIT 1;

In either case:

• If there are no rows found in the first SELECT, the result set will be filled with the values from the second SELECT.

• If one row is found in the first SELECT, the first SELECT values are offered in the result set and the second SELECT values are omitted.

*You don't have to assign a column name/alias to the second SELECT values if you want to use the same columns (in the same order) from the first SELECT.

*UNION doesn't require the column names from the two united queries to be identical, in fact you can assign different column names to the second SELECT query if it helps your case. (Your result set processing code will need to accommodate for these column naming differences.)

(SELECT Col1,Col2,Col3 FROM ExampleTable WHERE ID='1234')
 UNION (SELECT 'Def Val' AS `Fallback1`,'none' AS `Fallback2`,'' AS `Fallback3`) LIMIT 1;

My opinion is that this is very easy to read and doesn't seem like a taxing query.

Answer 2

To handle a wider variety of cases, you'll need some conditional logic. This is only available in stored procedures in MySQL so you'll need to wrap this code in a procedure and call it:

if exists (
       SELECT `file` FROM `show`, `schedule` 
       WHERE `channel` = 1 AND `start_time` <= UNIX_TIMESTAMP() 
       AND `start_time` > UNIX_TIMESTAMP()-1800 AND `show`.`id` = `schedule`.`file` 
) then
       SELECT `file` FROM `show`, `schedule` 
       WHERE `channel` = 1 AND `start_time` <= UNIX_TIMESTAMP() 
       AND `start_time` > UNIX_TIMESTAMP()-1800 AND `show`.`id` = `schedule`.`file` 
       ORDER BY `start_time` DESC LIMIT 1
; else
        select `DefaultValue` as `file`
; end if

Answer 3

One way to do it

SELECT IFNULL(MIN(`file`), 'default.webm') `file` 
  FROM `show`, `schedule` 
 WHERE `channel` = 1 AND `start_time` <= UNIX_TIMESTAMP() 
   AND `start_time` > UNIX_TIMESTAMP()-1800 AND `show`.`id` = `schedule`.`file` 
 ORDER BY `start_time` DESC LIMIT 1

Since you return only one row, you can use an aggregate function, in that case MIN(), that ensures that you'll get NULL if no records selected. Then IFNULL() or COALESCE() will do its job.