In SQL: When 'no results' return from a query show some default results

Question

I'm using Postgres to get results from a table. I want to use SQL to solve a problem.

When my postgresql query returns no results, I would like some default row results to appear. I am using Jasper-reports, so I don't have a lot of tools to catch the event where there are no results, and then deliver default results.

When the first query DOES show results I do not want the default results to appear. Currently I'm trying to use a UNION but I want the second query after the Union command to ONLY happen if the first query shows 0 results.

Can this be done in SQL?

Answer 1

You could try a CTE:

with fooresults as
(
select c1, c2 from foo
)

select c1, c2 from fooresults
union
select c1, c2 from blah
where (select count(*) from fooresults)=0

OR you can test for empty fooresults without counting:

 where not exists (select 1 from fooresults) 

P.S. And of course you could add any conditions required when you instantiate the CTE:

      select c1, c2 from foo where .... <some conditions>