Return a default value in SQL even if the query return no result

Question

For a very specific need for my application, in the best scenario, I must always retrieve an additional record preceding the date filter that the user selected.

Suppose in my database I have orders with the following dates ('20220701', '20221231', '20230101', '20230701', '20240101') and the user wants all orders from 2023 (keeping in mind that I need an additional previous record).

On-screen user date filter... From: "20230101" To: "20231231"

Currently I am using the following query:

SELECT Ord1.OrderDate FROM Orders Ord1
WHERE Ord1.OrderDate BETWEEN (SELECT MAX(Ord2.OrderDate)
FROM Orders Ord2 WHERE Ord2.OrderDate < '20230101')
AND '20231231'
ORDER BY Ord1.OrderDate

Running the query I get: ('20221231', '20230101', '20230701') witch is perfect!!!

My problem is that let's assume that the user DID NOT place any order in 2022, so my database contains ('20230101', '20230701', '20240101'), at this point my query no longer works, because I do not "respect" the first condition in my BETWEEN clause, but I must still have the result ('20230101', '20230701').

Answer 1

What you want is some kind of NVL function over subqueries, like this:

SELECT NVL(
    (SELECT MAX(Date) FROM Table1 WHERE Date < '20240101'),
    (SELECT MIN(Date) FROM Table1)
) d

That way, if the first subquery returns NULL, the outer query will return the result of the second subquery.

The exact syntax will depend on your DBMS. For example, in Oracle you would need "FROM DUAL" for it to be valid. COALESCE() might work if your DBMS doesn't have NVL()

Answer 2

This could be accomplished with a union:

SELECT OrderDate
FROM Orders
WHERE OrderDate BETWEEN '20230101' AND '20231231'
UNION ALL
SELECT MAX(OrderDate)
FROM Orders
WHERE OrderDate < '20230101'
ORDER BY Ord1.OrderDate;

The minor downside is having to repeat the date but you might find this performs better.

Answer 3

With all your help, here is how I resolve it:

SELECT T1.OrderDate
FROM Orders T1
WHERE T1.OrderDate BETWEEN
    COALESCE((SELECT MAX(T2.OrderDate)
    FROM Orders T2 WHERE
    T2.OrderDate < '20230101'), '20230101')
AND '20231231'

Answer 4

One of the many universal ways to solve your problem. The solution returns values from all columns of the orders table, not just the values of the orderdate column. But if you had told us which DBMS you were using, the solutions would have been more optimal.

select
  *
from orders
where
  orderdate in (
    select max(orderdate)
    from orders
    where orderdate >= '20220101'
      and orderdate < '20230101'
    union all
    select orderdate
    from orders
    where orderdate >= '20230101'
      and orderdate < '20240101'
  );

Try it on db<>fiddle.