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?

Ge

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.

Hoi

Brilliant Stuff! But how can this concept be applied to a query that returns multiple rows instead of one as in our case? – Surfeit

This is not really a good solution as Tomas explains below (except probably if you query on a primary key value). – Eastereasterday

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.

Debit

This works very nicely for me. I needed to do an aggregate (MAX) with a predicate and if there were no rows matching the predicate, I'd get no rows returned. Now I get the default back instead. – Divisionism

This does not work if the first query returns "1" and the second (default) select returns "0" and "1". The final result would be "0" instead of "1". – Longford

@Debit See db-fiddle.com/f/9jZiSLa5L9tCD7pmXwFiYW/0 – Longford

@Fir that query does exactly what I am saying it should. Because the first subquery has a qualifying row, the subquery after the UNION is ignored. This is just a misunderatanding. I'll be honest, I've never seen that syntax before (VALUES (0),(1)) t (c1) I understand that t is the derived table's alias, but I have no idea what c1 is doing. – Debit

@Debit The problem with your solution is that it does only work when the first select returns exactly 1 or 0 rows and the default value is also a single row and not two or more. You may want to extend your solution so it works also in these cases (just for other people looking at the issue). Also change t1 in my fiddle to t2 or other values and see what happens. – Longford

Furthermore, when city is t1, t2, t3, or t4, then one of the respective qualifying rows will be returned. You cannot guarantee which one for t3 and t4 because you are not ordering those rows. This means that the SQL engine can choose which ever one it likes. Only when city is t0 does the derived table get any attention. And again, the engine decides which value to put forward as the "first row". – Debit

There is no flaw in my snippet when your query logic aligns with the intention to return one row -- like when the first subquery uses a primary key in the WHERE and the fallback subquery is also designed to return a single record. – Debit

Let us continue this discussion in chat. – Longford

I think there should be an order by because we are using limit and we are not sure that when there are two rows, the default row will be excluded unless it comes after the other one. – Grevera

I don't disagree with you @Ludovic, but this is a side-argument (that I've already had) from what my snippet intends to do. My snippet is only designed to return a default row when the query logic guarantees a maximum number of rows to be one. For instance -- when you query based on a Primary/Unique Key. This answer is not built to accommodate scenarios when there may be zero, one, or more than one row in the result set. I have already tried to make this clear in my answer. I am not trying to deceive anyone. – Debit

Yes I was talking about the case where the query logic returns 0 or 1 row. Let's say you get one row and then the union with the default values produces another row which gives you a total of 2 rows. Then limit "might choose" the wrong one. Maybe there is something I did not understand properly. – Grevera

Oh. I assume that the first written query before the UNION will be ordered first and the query after UNION will be ordered second. Have you encountered a result set where the default row has actually been put before the real row? If so, I'd like to understand how it happened so that I can reproduce the unintended result. @Lud – Debit

Unless there is an order by, there is no guarantee about the order of rows returned by a query (SQL standard). See ORDER BY and LIMIT in Unions: "(...) because UNION by default produces an unordered set of rows" and also "To cause rows in a UNION result to consist of the sets of rows retrieved by each SELECT one after the other, select an additional column in each SELECT to use as a sort column and add an ORDER BY that sorts on that column following the last SELECT". – Grevera

Answer 3

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

Pacifier