SQL JOIN with COALESCE in condition is duplicating rows

Question

I am trying to join two tables together. The first table contains data records that I do not want to duplicate. The second table I am joining to the first table to lookup a [value] by a distinct [profileId] and [role]. The [profileId], [role] column in the second table has a unique constraint on the combination, but [role] can sometimes be NULL, in which case I treat that value as the default for that profile.

How can I join these tables together without duplicating the rows, and without using multiple left joins? My actual query is more complex than the example.

See example below.

DECLARE @temp TABLE ([profileId] int, [role] int)
DECLARE @temp2 TABLE ([profileId] int, [role] int, [value] nvarchar(50))

INSERT INTO @temp ([profileId], [role]) VALUES (1, 1)
INSERT INTO @temp ([profileId], [role]) VALUES (1, 2)
INSERT INTO @temp ([profileId], [role]) VALUES (2, 1)
INSERT INTO @temp ([profileId], [role]) VALUES (2, 2)
INSERT INTO @temp2 ([profileId], [role], [value]) VALUES (1, 1, 'MATCH')
INSERT INTO @temp2 ([profileId], [role], [value]) VALUES (1, NULL, 'DEFAULT1')
INSERT INTO @temp2 ([profileId], [role], [value]) VALUES (2, NULL, 'DEFAULT2')

SELECT
    T1.[profileId],
    T1.[role],
    T2.value
FROM
    @temp T1
    JOIN @temp2 T2 ON T1.profileId = T2.profileId AND COALESCE(T2.[role], T1.[role]) = T1.[role]

This gives me (and I understand why)

================================
| profileId | role  |  value   |
================================
|     1     |   1   |  MATCH   |
--------------------------------
|     1     |   1   | DEFAULT1 |
--------------------------------
|     1     |   2   | DEFAULT1 |
--------------------------------
|     2     |   1   | DEFAULT2 |
--------------------------------
|     2     |   2   | DEFAULT2 |
================================

While I want

================================
| profileId | role  |  value   |
================================
|     1     |   1   |  MATCH   |
--------------------------------
|     1     |   2   | DEFAULT1 |
--------------------------------
|     2     |   1   | DEFAULT2 |
--------------------------------
|     2     |   2   | DEFAULT2 |
================================

Answer 1

This SQL works fine:

SELECT
    T1.[role],
    Value = coalesce(max(nullif(T2.value,'DEFAULT')),'DEFAULT')
FROM
    @temp T1
    JOIN @temp2 T2 ON COALESCE(T2.[role], T1.[role]) = T1.[role]
group by
    T1.[role]
;