condition in the SQL request-CodePudding

I have a SQL request:

SELECT * 
FROM Table1 
JOIN Table2 ON Table2.ID = Table1.Table2_iD
WHERE 
    *conditions*
    AND *idk how to describe it in sql*

I need a condition that will do something like this:

IF Table1.Date IS NOT NULL THEN
    Table1.Date BETWEEN date1 AND date2
ELSE 
    Table2.Date 2 months BETWEEN date1 AND date2

I need your help my friends

CodePudding user response：

You could COALESCE the Date Field like below. Coalesce checks for NULL values and then moves to the next value if it is null. It also works for N about of fields.

WHERE COALESCE(Table1.Date,ADD_MONTHS(Table2.Date,2)) BETWEEN date1 AND date2

CodePudding user response：

The code you provided is a bit messy, but I think you are looking for this:

WHERE CASE WHEN Table1.Date IS NOT NULL 
           THEN Table1.Date 
           ELSE ADD_MONTHS(Table2.Date, 2) END BETWEEN date1 AND date2

Or shorter:

WHERE COALESCE(Table1.Date, ADD_MONTHS(Table2.Date, 2)) BETWEEN date1 AND date2

CodePudding user response：

I would suggest doing a simple UNION ALL:

SELECT * 
  FROM Table1 t1
       JOIN Table2 t2 ON t2.ID = t1.Table2_ID
 WHERE Table1.Date IS NOT NULL
   AND Table1.Date BETWEEN date1 AND date2
 UNION ALL
SELECT * 
  FROM Table1 t1
       JOIN Table2 t2 ON t2.ID = t1.Table2_ID
 WHERE Table1.Date IS NULL
   AND Table1.Date   2 BETWEEN date1 AND date2;

since you will not render a possible index on Table1.Date unusable and have the same output. Performance-wise, it could potentially be better than using it with a function.