Return a default value if single row is not 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 SQL that 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

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.

Answer 2

@peterm's answer and this answer are designed to accommodate SQL logic that will return a maximum of one row in the result set.

His answer is designed to return a single row with a single column.

My answer is designed to return a single row with one or more columns.

Essentially, you can just use UNION with hardcoded value(s) in a second SELECT clause that has the same number of columns as the first SELECT.

For the OP:

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

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

---

As a more general example: (DB-Fiddle Demo)

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

Outcomes:

• If there are no rows found in the first SELECT, the result set will be filled with the values from the second SELECT. The result set as an array would be:

  [['Col1' => 'Def Val', 'Col2' => 'none', 'Col3' => '']]
  

• 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. The result set as an would be: (see my demo link)

  [['Col1' => 'A', 'Col2' => 'B', 'Col3' => 'C']]
  

*The associative keys in the result set will be dictated by the column names / aliases in the first SELECT query.

*Subsequent SELECT queries do not need to bother nominating column aliases.

*UNION doesn't require the column names from the two united queries to be identical. In fact, the column names or data sources in subsequent SELECT queries may be anything (different columns, function calls, etc).

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

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

---

Thanks to @LudovicKuty for catching a potential bug regarding the order of rows produced by the UNION. https://dev.mysql.com/doc/refman/8.0/en/union.html#union-order-by-limit To eliminate the possibility of the default row being ordered before the found row, write an ORDER BY clause with enough logic to ensure the default row is always ordered later in the result set. There will be different sql-dialect-specific syntaxes that can be used to identify the default row. In some ORDER BY columnName = 'default value' will be enough, others may demand IF or IIF or CASE, etc. So long as the you build the logic so that the default returns a truthy result, then true will be treated as 1 and false will be treated as 0 -- and of course 1 comes after 0 when sorting ascending.