Coalesce two SQL queries

Question

I need an SQL query which mimics an if-then-else statement of the form:

if (query1 != null)
  return query1
else
  return query2

Since COALESCE won't work with result sets, I created a union query that does the job:

SELECT * FROM obs WHERE cond1  --query1
UNION
SELECT * FROM obs WHERE (NOT EXISTS(query1)) AND cond2

In SQL:

  ( SELECT * FROM obs WHERE src = @id AND tstart <= @instant AND tend >= @instant )
  UNION
  ( SELECT * FROM obs WHERE (NOT EXISTS (SELECT 1 FROM obs WHERE src = @id AND tstart <= @instant AND tend >= @instant )) AND src = @id AND tstart <= @instant ORDER BY tend DESC LIMIT 1);

Table obs has the fields ( src | tstart | tend | ... ). I want to select those rows that overlap with @instant. If no overlapping rows were found, the closest row before @instant should be returned.

The SQL UNION statement works but it is very clumsy and I am looking for a shorter and clearer statement. Something in the spirit of COALESCE ( query1, query2 ) would be nice. My database is Postgresql.

Answer 1

First, union all is probably more appropriate than union in this case.

Second, you can express this using with to simplify the queries:

WITH t1 as (
      SELECT *
      FROM obs
      WHERE src = @id AND tstart <= @instant AND tend >= @instant
     )
SELECT t1.*
FROM t1
UNION ALL
(SELECT *
 FROM obs
 WHERE NOT EXISTS (SELECT 1 FROM t1) AND
       src = @id AND tstart <= @instant
 ORDER BY tend DESC
 LIMIT 1
);

But, if you are looking for a single row, this is simpler:

 SELECT *
 FROM obs
 WHERE src = @id
 ORDER BY (CASE WHEN  tstart <= @instant AND tend >= @instant THEN 1
                ELSE 2
           END),
          tend DESC
 LIMIT 1;

And, if not a single row, then window functions can also be used:

SELECT o.*
FROM (SELECT o.*,
             DENSE_RANK() OVER (PARTITION BY src
                                ORDER BY (CASE WHEN tstart <= @instant AND tend >= @instant THEN 1
                                               ELSE 2
                                          END),
                                         (CASE WHEN tstart <= @instant AND tend >= @instant THEN NULL
                                               ELSE tend
                                          END) DESC
                               ) as seqnum
      FROM obs o
      WHERE src = @id
     ) o
WHERE seqnum = 1;