If you want to be able to define overrides for both instance+database and instance-only, but want an instance+database match to supersede an instance-only match, you will need additional logic.

Two options come to mind:

1. Define two joins and use COALESCE() to select the preferred match.
2. Use an OUTER APPLY(SELECT TOP 1...ORDER BY) to pick the match of higher priority.

Something like:

select i.instance_name, d.database_name, coalesce(s1.i, s2.i) as Value
from @instance i
join @db d on i.instance_name = d.instance_name
left join @override s1 on
    d.instance_name + '.' + d.database_name = s1.name
left join @override s2 on
    d.instance_name = s2.name and s2.database_name IS NULL

or

select i.instance_name, d.database_name, s.i as Value
from @instance i
join @db d on i.instance_name = d.instance_name
outer apply (
    select top 1 s.*
    from @override s
    where d.instance_name + '.' + d.database_name = s.name 
        or (d.instance_name = s.name AND s.database_name IS NULL)
    order by case when s.database_name is not null then 1 else 2 end
) s

(Formatting in style of original post, before it was mass-reformatted.)

Results:
| instance_name | database_name | Value |
| ------------ | ------------ | ------------ |
| inst1 | db1 | a |
| inst1 | db2 | b |

The second unwanted inst1.db1 result has been eliminated. See this db<>fiddle for a working demo.