Search code examples
sqlhiveprestotrino

Presto: Return another table or a dummy value/table if original query returns no rows

I am learning SQL on Presto/Hive. I would like to ask if there is any way that I can return another select table if my original query returns no rows?

Something like --

if (select ORIGINAL_QUERY returns no rows) then (select ALTERNATE_QUERY).

Attempts:

I understand that there is such a thing called IF EXISTS but I can't seem to make it work on a select statement execution? Presto resources are limited out there so any help and guidance would be appreciated!

This is not a homework assignment. Just trying to solve some PrestoSQL challenges.

Solution

  • You can use union all and CTEs:

    with q1 as (
      <first query here>
     )
select q1.*
from q1
union all
select q2.*
from q2
where not exists (select 1 from q1);

    Note: This assumes that the queries return the same number and types of columns.