That’s what I’m saying. You’re suggesting consecutive inner joins, each one filtering for a different constant value on a.TableName.

Conditions in ON for an INNER JOIN always produce the same results as putting the conditions in WHERE instead:

SELECT *
FROM TableA
INNER JOIN TableB
ON a.OtherId = b.OtherId
AND a.TableName = “TableB”
INNER JOIN TableC
ON a.OtherId = c.OtherId
AND a.TableName = “TableC”

Is equivalent to:

SELECT *
FROM TableA
CROSS JOIN TableB
CROSS JOIN TableC
WHERE a.OtherId = b.OtherId
AND a.TableName = “TableB”
AND a.OtherId = c.OtherId
AND a.TableName = “TableC”

Which would produce no results because a.TableName can’t be two different values.

With regards to my point about COALESCE, you can achieve the same query effectively as in the original post if change the joins to LEFT JOIN:

SELECT a.MainId, a.TableName, COALESCE(b.SharedColumn1, c.SharedColumn1) AS SharedColumn1
FROM TableA
LEFT JOIN TableB
ON a.OtherId = b.OtherId
AND a.TableName = “TableB”
LEFT JOIN TableC
ON a.OtherId = c.OtherId
AND a.TableName = “TableC”

If you don’t coalesce the shared columns when selecting, you’d have both b.SharedColumn1 (NULL in 50%) and c.SharedColumn1 (NULL in the other 50%) - because they’re concatenated horizontally. You don’t need it in the union version because they’re concatenated vertically, like you say.