Join on matching values or else use default value-CodePudding

I'm looking to do an inexact match between two tables. As in the case below, if there's not an exact match by ID, then we match on the value of -1 from the Schedule table. The join below works but it's a cumbersome way to do it.

Is there a better way to handle this? Thanks.

DROP TABLE IF EXISTS #schedule
CREATE TABLE #schedule (
 DayID int NULL,
 DayValue varchar(10) NULL
)
INSERT INTO #schedule (DayID, DayValue)
VALUES ('-1','A'),
        ('1','B'), 
        ('2','C'), 
        ('3','D')

DROP TABLE IF EXISTS #data
CREATE TABLE #data (
 DayID int NULL
)
INSERT INTO #data (DayID)
VALUES ('1'),('2'),('3'),('4'),('5')

SELECT *
FROM #data D
LEFT JOIN #schedule S
    ON CASE WHEN D.DayID NOT IN (SELECT DayID from #schedule S) THEN -1 else D.DayID end = S.DayID

CodePudding user response：

I would simply always JOIN to the default row, but then use COALESCE to only give the default row's value is the related row wasn't found:

SELECT D.DayID,
       COALESCE(S.DayValue,Sd.DayValue)
FROM #data D
     JOIN #schedule Sd ON Sd.DayID = -1
     LEFT JOIN #schedule S ON D.DayID = S.DayID;