Alternative to COALESCE() in join condition?

I have a SQL query that is doing a join like the one shown below:

SELECT
  TableA.Id,
  TableB.Column1,
  TableC.Column2
  FROM TableA
    INNER JOIN TableB
      ON TableA.Id = TableB.Id
    INNER JOIN TableC
      ON COALESCE(TableB.Column2, TableB.Column3) = TableC.Id

Is there any way to get rid of the COALESCE() in this join? I have it there in the first place because a row in TableB could have both TableB.Column2 and TableB.Column3 populated, and if this is the case we should be respecting the value in Column2 when performing the join (i.e. the value in Column2 takes priority over the value in Column3).

The query is having some performance issues, though, and from some searching it seems like the COALESCE() in this join could be confusing the query optimizer. Any suggestions, or is this just doomed?

I don’t really know what I should be doing to preserve the logic but improve query performance in this case.

You need to sign in to view this answers